Solved

Disfunctional Relational Database

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now