May 9, 2011 at 9:32 am
I have four tables that store contact info:
Suppliers
Wholesalers
SalesReps
Retailers
The data stored in these four tables is different enough that it requires separate tables (i.e. it doesn't make sense to store them all in the same table with a sub-type column).
I have a fifth table that needs to store web accounts (i.e. usernames and passwords for a web portal):
WebAccount
The WebAccount table needs to reference a record from any one of the four 'contact' tables, i.e. Retailers, SalesReps, Wholesaler, Suppliers.
I'm looking for suggestions on how to design for this situation so that referential integrity is maintained.
I know that a foreign key referencing any one of the four PK values from the contact tables isn't possible.
Also, it doesn't seem right to have four FK columns in the WebAccount table, since only one of them will ever be populated, and I'd have to write a complicated CHECK constraint to make sure of that.
Any insight/suggestions would be greatly appreciated.
Regards,
-Simon
May 9, 2011 at 12:07 pm
Maybe a sequence table? You'd then FK all your other tables back to it.
http://www.mssqltips.com/tip.asp?tip=1293
_____________________________________________________________________
- Nate
May 9, 2011 at 1:29 pm
Hi Nate,
Thanks for the reply.
I already have data in my contact tables, so I don't really see how this would help.
Maybe I'm missing your point?
- Simon
May 9, 2011 at 2:34 pm
Sorry, kicked it out there thinking this was new development work.
In the past I've had to do something similar using a JOIN table containing a generic ID plus an identifier column where the "FK" validation is done in code. Not pretty but it worked.
create table dbo.tbl (
WebAccountId INT
, FKId INT
, FKIdType INT)
_____________________________________________________________________
- Nate
May 9, 2011 at 2:42 pm
Thanks for the clarification, Nate.
I feel like I'm stuck with choosing between two bad options, namely:
1) Eschewing referential integrity via Foreign Keys by not defining them (Foreign Keys) on the WebAccount table (your suggestion), and writing my own hacky trigger code to enforce RI.
2) Enforcing referential integrity by creating four columns with Foreign Keys defined, but having to write some hacky CHECK constraints to make sure that only one of the four columns is populated.
I'm currently leaning toward option 2....
-Simon
May 9, 2011 at 3:09 pm
just my 2ct to solve a design flaw...
- Since it can only hold one the the 4 key values, maybe actually design 4 columns with fk ref to their corresponding pk table and incorporate a calculated column which actually just is a coalesce of the 4 columns
Be sure to check content only allowed for one column via a constraint !
-4 fk = 4-fkindexes ! + maybe even an index for the computed column.
- Or for tables each holding their own FK info unioned in a view ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 9, 2011 at 7:53 pm
So I refer to this as an interface (as I believe it is close enough to the more object oriented version of the word.) Basically, you have four completely independent entities:
Suppliers
Wholesalers
SalesReps
Retailers
That have one thing in common. WebAccount information. Instead of thinking about the webAccount as being owned by each account, think of it is a thing of it's own that each of these objects uses. You already stated that they don't make proper sub class objects, and I kind of agree. Each of these things might be a proper subclass as they are all companies/people you might do work with, but in any case, you can't say that a Supplier Is A WebAccount, or vice versa, and if you can't make such a statement, then a subclass you shouldn't design, particularly just so you can implement one bit of functionality.
>>I have a fifth table that needs to store web accounts (i.e. usernames and passwords for a web portal):
WebAccount
The WebAccount table needs to reference a record from any one of the four 'contact' tables, i.e. Retailers, SalesReps, Wholesaler, Suppliers.<<
My suggestion is to create the tables like this:
create table WebAccount
(
WebAccountId int primary key, --or whatever key
...
)
create table Retailer
(
RetailerId int primary key, --or whatever key
...
WebAccountId int references WebAccount(WebAccountId)
)
Now you have the reference to the WebAccount for use to know their information. And if you need > 1 webAccount referenced, you make a table RetailerWebAccount with a key of WebAccountId, RetailerId...
Hope this helps
May 9, 2011 at 8:05 pm
One other thing. If you want to make sure that the webAccount is exclusive, consider either a trigger or check constraint. Most people don't care.
You can build a view to get that view of the data that shows where it was used, and how if you want. It is clearly all up to usage, but using the pattern mentioned in my prev post gives you the ability to implement all web account usage the same, and make sure you don't have dups... The only dups that matter are the ones across entities.
May 10, 2011 at 10:58 am
drsql (5/9/2011)
My suggestion is to create the tables like this:create table WebAccount
(
WebAccountId int primary key, --or whatever key
...
)
create table Retailer
(
RetailerId int primary key, --or whatever key
...
WebAccountId int references WebAccount(WebAccountId)
)
Now you have the reference to the WebAccount for use to know their information. And if you need > 1 webAccount referenced, you make a table RetailerWebAccount with a key of WebAccountId, RetailerId...
I would agree with this, especially the last part of using another table for each reference. Since you were initially saying you were going to put the reference in the WebAccount table, it seems that one account would only have one retailer (or other record), but I would imagine that there is not a limit to how many accounts a certain retailer could have. Therefore, a RetailerWebAccount reference table would seem to be the best bet. This would also be able to be used if the logic needed to be changed so that one account could be linked to multiple types.
Using a TypeID to identify which table the PK is in is clunky, and you won't be able to set up the relation in the DB. Having a FK column for each type of reference isn't scalable, because you'd have to add another column if you added another table that could be referenced.
May 11, 2011 at 10:03 am
Thanks so much for all of the replies - I'm very grateful for the ideas/contributions.
I'm going to digest everything and try to come-up with something later this afternoon.
I'll post back here to let you guys know what I went with.
Thanks again!
-Simon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply