Design: Using 1 table as a source for 3 others

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Design: Using 1 table as a source for 3 others

Author

Topic

Aster
Starting Member

3 Posts

Posted - 2008-01-20 : 20:55:55

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

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-01-21 : 06:58:25

can a company, client, vendor have more than 1 phone number??? Your data model doesn't seem to allow this....

how do you intend to handle head/branch office numbers? fax/telex numbers?

Aster
Starting Member

3 Posts

Posted - 2008-01-21 : 11:12:12

quote:
can a company, client, vendor have more than 1 phone number??? Your data model doesn't seem to allow this....

how do you intend to handle head/branch office numbers? fax/telex numbers?

Yes they can have multiple phone numbers. That is where the Parent_Key column of Phones comes in.


Phones:
Phone_Key	Parent_Key	Phone_Number	Phone_Type	Primary_Flag
1		1		222-333-4444	'Phone'		'Y'
2		1		222-333-4445	'Fax'		'N'
3		1		222-333-4446	'Cell'		'N'
4		4		333-666-9999	'Phone'		'Y'
4		4		333-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_Key	Vendor_Name		Phone_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:
Select	p.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_Number	Phone_Type	Primary_Flag
222-333-4444	'Phone'		'Y'
222-333-4445	'Fax'		'N'
222-333-4446	'Cell'		'N'

I am just not sure if there is a cleaner way to handle this without resorting to adding more tables to handle the relationships so I can make use of the constraints. This way I can have a foreign key reference, but it is backwards of a standard one to many relationship.

Also I have to handle changing of the primary/parent key data if the user decides to rid himself of one of the phone numbers, so more code to write that will maintain the integrity.

Hopefully this better explains my problem.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2008-01-22 : 13:36:16

You could use Party as a super-type of Client, Vendor and Company and then hang have a many to many to put Parties with Phone numbers. I actually tend to use AccessPoint as a supertype of Phone, Street Address, Email Address and such and then use a PartyAccessPoint m2m ...

Jay
to here knows when

Aster
Starting Member

3 Posts

Posted - 2008-01-22 : 14:23:59

Page that is a very good point. Perhaps I am going about my setup and definitions in the wrong way. Which means I will need to re-think my entire approach to storing customer contacts(People), business contacts, such as vendors (With their people), and internal contacts.

I will likely have to resort to intermediary tables for a lot of things such as phone numbers and addresses, but instead of trying to store the entities that actually own the address and phone numbers in so many different ways I should find ways to keep similar data together.

Your mention of supertypes has me thinking so I will go out and read up on this concept some more as I am not as familiar with it as I probably should be.

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources