Solved

Disfunctional Relational Database

Posted on 2012-03-13
5
999 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

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.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

774 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