Table Relation

[Miro]

VS2008
I have created 3 tables.

Vendors
Customers
PhoneNumbers

each have their own key
Vendor has: VendorID - int unique identifier
Customer has: CustomerID - int unique identifier

PhoneNumbers have:
PhoneNumberID -int unique identifier
PhoneNumberType - varchar(1)
KeyID - int

I would like ( in the relation ) to linke the Customers Table and the Vendor
Table to the PhoneNumbers Table.
They would both link to KeyID, but PhoneNumberType would either be a letter
"C" or "V".

I cannot figure out how to set this up in the relation. If I could somehow
hardcode a letter in the PhoneNumberType in the relations dialog box...that
would be great - but I must be missing something.

I do not see a point in creating a VendorPhoneNumbers and
CustomerPhoneNumbers table as they will have the exact same columns.

Thanks,

Miro

 

[Miro]

I am starting to think I need to create an 'in-between' table.

Table: PhoneLinker
PhoneLinkerID -unique identifier
KeyID - links to VendorID or CustomerID

PhoneLinkerID would linke to the PhoneNumbers.KeyID field.

The other option I was mentioning is not prefered?

Miro

 

[Miro]

Have you considered having an Organization table instead of separate
Vendor and Customer tables? They are often very similar. You could put the
V or C in the Organization table to separate them. You might even create
separate fields for V and C, so that some organizations could be both - it
can happen.

At one point I want to add more tables that will have phone numbers - such
as.

CompanyDivisions - Yes this will have similar fields
Employee - This will not be simillar as it will be something like a quick
contacts list.

Later im assuming ill make an 'Email' table as well.

This program is not going to be used in the 'real world'. I just am trying
to re-create some things we do at work now to learn all the quirks about it.
Stumbled upon the multi-field key problem.

So far my only solution I have dreamt up with is to have a linker file such
as:

PhoneLinker
LinkerID -Unique - Links to the PhoneNumber File - Later an EmailFile ( and
so on )
CustID -links to customer
VendID - links to vendor
EmployeeID -links to employee

Basically in a record the PhoneID will be unique, and the other 3 fields 99%
of the time ( im assuming 100% ) will have only one of the fields filled in.

I am trying to poke holes in that now to see if thats the right way of going
about it.

 

[Miro]

Why not have multiple phone numbers per user/customer/vendor?

My stupid cell phone allows 1 cell phone number, 1 lan, and 1 alternate.
That is not enough in some cases as some people have multiple phones /
offices.

So thats why I would like to create the structure as a 1 to many/(infinite)
phone numbers relation.
I do not want to create a 'field' for each 'Customer / Vendor' to store
numbers. I would like a file relation.

I just couldnt seem to do it my original thought idea way, and the only
solution I am having is to actually create a linker table.

Wondering if the same problem was out there to anyone else who has had to
create a 1 to many relation what the solution was.

I can just as easily create a "CustomerPhoneNumbers", "VendorPhoneNumbers",
"EmployeePhoneNumbers" table, and each would have the exact same fields.
But what would the point be then? I beleive it would be more proper to
store all that in 1 table, and link it to the proper master table as
required.

In this case normalization seems to be the right course of action and not to
denormalize it.

Miro

 

[Sam Hobbs]

Wondering if the same problem was out there to anyone else who has had to
create a 1 to many relation what the solution was.

Database design has existed for over 30 years and nearly a half a century.
Your requirements have been solved long before.

This question would get better answers in a database group or forum.
Microsoft has database newsgroups, I assume, and MSDN database forum(s).
There are also other sites, such as:
http://www.dbforums.com.

 

[Miro]

"Database design has existed for over 30 years and nearly a half a
Wow computers can do that now?
Thanks for that great information.

My solution was to create 3 seperate tables.

M.