Solved

Disfunctional Relational Database

Posted on 2012-03-13
5
1,006 Views
Last Modified: 2012-03-20
I'm working on reports and I'm finding most of the resources on the net are basic using the wizard or using one or two databases and I'd like something more indepth for my reports.  I'm using a relational database on MS SQL Server 2008 and BIDS to develop and the users will access the reports through our Sharepoint site.  There is also a lot of information on new cube databases and warehousing but the system we have is tied to an accounting program so its not likely we'd upgrade to newer technology or correct any database design issues.

I'm finding that some relationships set up in the database are incorrect.  I'll have two primary keys linked (ie. client (ID) tied to invoice (ID) and then down a few fields will be a ClientID).  When designing the query I'll correct these but will my data still be incorrect?
0
Comment
Question by:HSI_guelph
  • 3
  • 2
5 Comments
 
LVL 25

Expert Comment

by:jogos
ID: 37715821
You mean there is no foreign key from Invoice.ClientId to Client.Id.
And there looks to be a foreign key from Client.Id to Invoice.ID.

You don't have a FK-relation to join tables together as long as the Invoice.ClientId has a the good content for finding the record in Client.  But when there is no FK then you don't have a guarantee the value will exists.

The accounting program will not rely on the FK-relations for giving an error and to  for input.

But when FK's defined to a wrong PK can give nasty errors when you want to delete a record (and certainly when it's defined as cascading delete).
0
 

Author Comment

by:HSI_guelph
ID: 37716212
When I go into Microsoft SQL Server Management Studio in dbo.Clients I clicked on Keys and it shows a key symbol with IX_Clients_ID in it.  If I open up the columns and look at ID it says Primary Key is False.  When I select dbo.Clients or the top layer of the database I get greyed out database buttons (relationship one in particular I was hoping to see).  I can't access Database Diagrams so I'm not sure how to see the relationships inside the server itself.  I can see a Primary Key in the Invoice table when I expand the columns and 2 items under Keys (gold key icon beside PK_Invoice and blue upside down key with IX_Invoice_ID next to it).  Perhaps I should use the CltNum to relate the two tables in my query.

Would the fact there is not a primary key in the clients table affect my query results?
dbClientsInvoice.jpg
0
 
LVL 25

Accepted Solution

by:
jogos earned 500 total points
ID: 37716623
If the application or a unique index prevent doubles, no wories. Not semantical correct but you can have 100 tables without primary key and handle everything yourself. Database engine lacks info to optimal interpretation .... but it works.


And the link from WIP to your client table do the both joins on id and on num and compare.

select w.id ,c.id , w.Wcltid, c.cltnum , w.Wcltnum
from dbo.wip as w
left join dbo.Client as c on c.id = w.Wcltid
order by w.id ,c.id , w.Wcltid

select w.id ,c.id , w.Wcltid, c.cltnum , w.Wcltnum
from dbo.wip as w
left join dbo.Client as c on c.cltnum = w.Wcltnum,
order by w.id ,c.id , w.Wcltid
0
 

Author Comment

by:HSI_guelph
ID: 37726160
The Clients.ID match the WIP.WCltID.
The Clients.Cltnum match the WIP.WCltNum.
(for both of your queries)

I can use Cltnum and WCltNum but I'm wondering if the data is correct, if there are issues with data that has been stored related to no primary key being set.
0
 

Author Closing Comment

by:HSI_guelph
ID: 37744197
Thanks for replying!  The database is a mess of repeating data and unclarified columns.  I'm accessing all the resources (people) I can to know where the information I need is stored.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question