Disfunctional Relational Database

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?
HSI_guelphAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jogosCommented:
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
HSI_guelphAuthor Commented:
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
jogosCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HSI_guelphAuthor Commented:
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
HSI_guelphAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.