January 20, 2008 at 6:07 pm
I have a general design question that has me a little stumped.
I have tried doing a number of searches on this subject, but have so far come up with nothing close. Maybe I am just using the wrong keywords, so if this has been covered before I apologize in advance.
The idea is that I have one table that can hold many records in a group, that will then be referenced in numerous other tables in the database. This way I can have a single table to hold this information instead of one for each other table that will reference it.
As an example. I have three tables.
Clients
Vendors
Company
Each of these tables need to have a pointer to a group of phone numbers. Each client, vendor, or my company could have multiple phone numbers, phone, fax, cell etc. I need to be able to maintain foreign key relationships as I don't want to end up with orphans in the phone table.
I can't use a foreign key field in the phone table with links to all three other tables.
I also don't want to create three link tables which would hold the keys to the clients table, and the keys to the phone table.
One thing I did think about is a structure similar to this:
Company:
Phone_Key int (Foreign Keyed to Phone_Key in Phone)
Other company info fields.
Vendors:
Phone_Key int (Foreign Keyed to Phone_Key in Phone)
Other Vendor info fields.
Clients:
Phone_Key int (Foreign Keyed to Phone_Key in Phone)
Other client info fields
Phone:
Phone_Key int PK identity
Parent_Key int FK to Phone_Key
Other info for the phone info
So I create the first phone number entry in the Phone table and assign it's key to the client's table Phone_Key field. (Or any of the other two table's records)
Next I create any additional phone entries in the Phone table assigning Parent_Key the primary number's Phone_Key.
Is this an acceptable approach, or is there a better less convoluted method for designing the table structure?
Second if this is good, how can I configure SQL server to have a default value for Parent_Key which matches the row's Phone_Key value if I don't provide a parent_key value during the insert? I would prefer to not use triggers at this point.
I know that I would have to deal with logic to handle deleting the primary phone_key from the group, but that is doable easily enough.
Thank you,
Chris
January 20, 2008 at 7:27 pm
your approach is certainly acceptable. i would approach it slightly differntly though.
if u want all phone #s in one table, then i'd do it like this (with the disclaimer that i might change my mind if i knew more about your application)
EntityTypes
- id tinyint PK
- description varchar(32) unique --(client, company, vendor)
Phones
- EntityType tinyint FK ---> Entity (id)
- EntityID int -- would hold custID for ex.
- phone char(10)
where entity is client, vendor, company
The only problem with this approach is that we cant FK entityId because it could point to either clients, vendors or companies. But a check constraint could ennforce it easily enough
January 20, 2008 at 8:59 pm
Yes I had thought about that, but it gives me an extra table I think, unless I am miss-reading your post. Also I can't directly foreign key the information.
Would be handy if I could foreign key against a column that is not unique...
I could do a compisite key then for Phone_Group_Key and Phone_Line_Key. The Phone_Group_Key could be non-unique for the column, but the phone_line_Key would be unique for that Phone_Group. If that makes any sense...
i.e.
PGK PLK
1 1
1 2
1 3
2 1
2 2
But I can't set another table's Phone_Group_Key value to foreign key against this Phone_Group_Key as it is not constrained as unique.
So is there a direct method in the table design to set a default value of a column to be the value of another column in the row/record?
Edit: Apparently not really a way to do the value assignment without creating a function to do it. So it is likely something to be handled in code.
January 20, 2008 at 11:33 pm
Hmm Russell, I think I someone misread your original intent. While your implementation would give one more table the number of entries are very limited. Wonder if there is a way to leverage SQL server's existing table ID information without getting too complex when retrieving the data.
In any event can you give me an example of the Check Constraint you would use to ensure the value in EntityID exists in the appropriate table? I would guess that you have some kind of a case statement which checks the EntityType then performs a look up against the appropriate table?
Thank you for your insight.
January 20, 2008 at 11:52 pm
Why are you storing the parent key at all? You already have the phone key in the parent table (vendors, etc) which is your link (along with the pkey of the phone number table) between the two tables.
I think you're over-engineering unfortunately.
When you're querying data, typically you'd find the vendor/customer/etc and then join that to the phone numbers table rather than searching for a phone number and linking it to a vendor, etc.
Also, storing the "parent key" in the phone number table means that you're making two separate joins (even if you can't physically create the phone->parent table join) and since they're
1:many and
many:1
you're effectively making it a 1:1 join. In that case, why even have the phone number table??
So don't worry about storing the parent key.
If you are in the situation where you have a phone number and wish to find matching vendors, customers, etc then you can do left joins from the phone number table to these tables, or do something like
select *
from phone_numbers PN
inner join
(
select customerID as [ID], customerName as [Name], phoneID as [phoneID], 'CUSTOMER' as [type] from customers
UNION ALL
select vendorID, vendorName, phoneID, 'VENDOR' from vendors
UNION ALL
...
) parents
on PN.phoneID = parents.phoneID
where PN.phoneNumber = '1234567890'
🙂
by the way, the article series on here that finished up in the past few weeks about database design handled this multiple table joining elegantly with views and constraints on those views. Do a google search over this site for
database design vet dog cat (this should be enough to find it).
good luck!
January 21, 2008 at 12:26 am
Thank you for posting Ian.
The issue is that a Customer or a Vender could have multiple phone numbers you need to keep track of. I need to know my vendors main number, their fax number, a cell phone number for a direct contact within the vendor organization etc...
At the same time all those same types of phone numbers may need to be tracked for the customer base as well.
So do I create multiple tables to store the phone number information so I can properly foreign key the Vendor_Key and the Customer_Key in each phone number table?
That is what I am trying to avoid. The extra tables to hold the same type of data. So I was storing the Parent_Key so that in the vendor table I store the primary phone key and to look up all the phone numbers I actually join the Vendor.Phone_Key to Phones.Parent_Key. This way I can retrieve all the phone numbers in the group. Kind of messy though.
I figure this kind of situation has got to come up where you have a one to many relationship, but the one could be from multiple tables referencing many records in a single table and still want to make use of the foreign key constraint to enforce integrity.
I will definately take a look at the artical, thank you for pointing it out.
January 21, 2008 at 1:36 am
Hi
Well your design is certainly a bit different from the usual way of doing things, but i reckon its a good design.
Do keep in mind the type of queries that will run againsr the table, indexes required and also the volume of data especially in the phone table.
"Keep Trying"
January 21, 2008 at 10:08 am
Chirag (1/21/2008)
HiWell your design is certainly a bit different from the usual way of doing things, but i reckon its a good design.
Do keep in mind the type of queries that will run againsr the table, indexes required and also the volume of data especially in the phone table.
Yeah that's where my rub is the setup is backwards of your standard one to many relationship. An example of how the data would be stored and retrieved goes something like this...
Phones:
Phone_KeyParent_KeyPhone_NumberPhone_TypePrimary_Flag
11222-333-4444'Phone''Y'
21222-333-4445'Fax''N'
31222-333-4446'Cell''N'
44333-666-9999'Phone''Y'
44333-666-9998'Fax''N'
Phone_Key is PK
Parent_Key is FK to Phone_Key (Same Table)
Phone_Type will actually be a FK to a list of valid types.
Vendors:
Vendor_KeyVendor_NamePhone_Key... (There are more but irrelevant)
1'Joes Supplies'1
2'Discount emporium'4
Vendor_Key is PK
Phone_Key is FK to Phones.Phone_Key
Getting phone numbers:
Selectp.Phone_Number,
p.Phone_Type,
p.Primary_Flag
From Phones p
inner join Vendors v
on p.Parent_Key = v.Phone_Key
where v.Vendor_Key = 1
Would return:
Phone_NumberPhone_TypePrimary_Flag
222-333-4444'Phone''Y'
222-333-4445'Fax''N'
222-333-4446'Cell''N'
Just feels like there should be a better way of going about this without having to write more code to maintain the data. Since I would need to write code to handle deleting the primary phone, and keepin the others.
January 21, 2008 at 11:17 pm
Hi
Another way would be to have a single phone table (no parentkey business) and have tables that stores the relation btween phone_key and vendor_key. So with this u will have 1 table each for vendor,company and clients.
This is a more normalised design.
"Keep Trying"
January 25, 2008 at 8:02 am
I've wrestled with the same issue as in the past and usually I just got around it by doing something else, such as making them into multiple columns in each of the tables. But then you don't have the functionality that you're asking for without union joins and potentially an unpivot in the event you want to put the numbers in the same column.
My question is; are you putting them in the same table trying to be more normalized? Or do you NEED them to be a separate Entity because you NEED to store additional information about a phone number. Type (cell,fax,primary) do not warrant a separate table. However if you were storing maybe Region or Date/Time of phone number updates than yes, break the phone number into it's on entity and thus table.
In the event that you split it to its own table then I would say out of all the idea posted (all good mind you) I would prefer the entity method with a constraint myself.
Just keep in mind when it comes to making views that you’ll end up doing a dynamic (or static meaning maintenance) pivot or a case statement (maintenance) to show vendor, customer, and etc information. Updates might also be more difficult as well along with Form development if you don’t turn the phones back into columns at some point. But again depends on your application.
Good luck and let us know how it turns out.
January 25, 2008 at 10:03 pm
My original thought was to have a specific table with which to draw all my phone number look ups from without needing to look at different tables depending on the actual owner of the phone records. However, this does not appear to lend itself to a normalized design where good integrity can be easily enforced.
After thinking about it some more I may just use intermediary tables for each actual entity that owns a phone number record. I will rework the way I store the individuals (people) so that customers actually point to records in a people table which in turn has a look up table to phone. Vendors will have a look up table with phones for those records that do not apply to any individuals within the vendor. For instance the main phone number or main fax number. Any direct lines will be handled through the vendor's relationship with the people table which has the before mentioned look up table to phones.
Employees can then be stored in the people table without a need to have a separate look up along with any other entity that could hold people.
This way I can tie a single person record with multiple other objects, such as vendors (whom some members could also be customers.)
Being that I am still in the design stages I do not know yet know what other objects may also relate, but hopefully this setup will make it easier to add other references as new ideas and requirements surface through the design phase.
Does this sound ok to you guys? Or am I not understanding some of the suggestions that have been made? I admit I am mostly unfamiliar with some mainstream descriptions as I have no formal education in the subject.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply