DB design Help!!

Question

DB design Help!!

Kayal-717623

Ten Centuries

Points: 1390
More actions
June 5, 2008 at 4:52 pm

#96034

I have 3 tables customer, vendor, Employee. All the 3 entities have users. The users table is linked to the vendor, customer,employee by UserLink table. The UserLink table uses the EntityTypes table to find out whether the user belongs to vendor, employee or customer. Hope I did not confuse u.
Say
Customer table
--------------
Customer#
Vendor table
-----------
Vendor#
Employee table
--------------
Employee#
User table
---------
User#
EntityType table
----------------
EntityTypeID# Entity Type
-------------------------------------
1 Vendor
2 Employee
3 Customer
UserLink Table
--------------
UserLinkID#
UserID
EntityTypeID
Enitity# -> which is customer# or vendor# or employee# depending on EntityTypeID
But the problem here is, its difficult to implement the userLink table as i cannot create a foreign key constraint on the Entity# to connect to vendor, customer or employee..In otherwords with this design i cannot create the referential integrity.
What could be the best solution to link the User table to vendor,customer and employee tables. I also want to store ONLY the entity information in main entity tables not the link information.
Please find attached the ER diagram of the above.
Your suggestions will really be helpful. Thanks for ur time.
Thanks,
Kayal

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736544 More actions · Answer 1

You can't make the FK reference multiple tables. It's a relationship between tables, not rows, so you can't switch on rows.

The way that I've done this is you have the common information in the user table, with the "type" that tells if it's a vendor, employee, etc. You can store the non-common data in a Vendordetails, EmployeeDetails, etc. table.

You'll need separate queries for each type when you need to get the non-common data.

Kayal-717623 Ten Centuries Points: 1390 More actions · Answer 2

Thanks Steve,

I like your idea but i might have situations where one user could be a part of customer and vendor. Its NOT one to one relationship always. Its one to many relationship. One user can be part of vendor employee or customer entity.

I appreciate your response and thanks for your time.

-Kayal

Steve Jones - SSC Editor SSC Guru Points: 736544 More actions · Answer 3

User

----

ID

Name

IsVendor

IsCustomer

IsEmp

Not the best solution, but it works.

Or you have

User

------

ID

Name

UserLink

--------

ID

IsVendor

IsCustomer

IsEmp

GSquared SSC Guru Points: 260824 More actions · Answer 4

You could have three columns (VendorID, CustomerID, EmployeeID) in the UserLink table, with an FK from each to the corresponding table. Then add a check constraint that makes it so one of them has to be not null. Might want something that says only one of them can be not null, or might want to consolidate data and make it so at least has to be not null, but more than one can be.

Would that do what you need?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Kayal-717623 Ten Centuries Points: 1390 More actions · Answer 5

Thanks Steve for your help. I appreciate it.

I understand ur approach. But the problem there is the user can be a Vendor A user or vendor B user or a Vendor C user. So in the link table i need to store the Vendor# which is a foreign key which means User A is a Vendor ABC user.

So UserLink table might look like this

UserLink

-------

UserLinkID

UserID

EntityType - possible values : vendor, employee, customer

Vendor#

Customer#

Employee#

Is this a good idea?

Thanks for ur time,

Kayal

Kayal-717623 Ten Centuries Points: 1390 More actions · Answer 6

GSquared,

This seems to be good solution. Is it possible to have a check constraint like that for 3 columns? .. Let me research more on that

I appreciate your help.

Thanks,

Kayal

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

There is aseries of articles on this site by Troy Ketsdever called Toward Integrity that might help you make a decision on the design. The first article is here: http://www.sqlservercentral.com/articles/Data+Modeling/61526/

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Kayal-717623 Ten Centuries Points: 1390 More actions · Answer 8

Thanks Jack :). This really helps.. I appreciate it.

-kayal