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" <mi**@beero.comwrote in message
news:Ov**************@TK2MSFTNGP03.phx.gbl...

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

[Steve Gerrard]

Miro wrote:

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.

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.

[Miro]

"Steve Gerrard" <my********@comcast.netwrote in message
news:YK******************************@comcast.com. ..

Miro wrote:

>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.

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.

[Patrice]

And the features you want are ? Multiple phone numbers per vendor or
customer ? Search someone just by it s phone number ? For now I would like
denormalize a bit this and would liekly store the phone number as an
attribute for each user.

--
Patrice

"Miro" <mi**@beero.coma écrit dans le message de groupe de discussion :
ur**************@TK2MSFTNGP03.phx.gbl...

"Steve Gerrard" <my********@comcast.netwrote in message
news:YK******************************@comcast.com. ..

>Miro wrote:

>>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.

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
"Patrice" <http://www.chez.com/scribe/wrote in message
news:69**********************************@microsof t.com...

And the features you want are ? Multiple phone numbers per vendor or
customer ? Search someone just by it s phone number ? For now I would like
denormalize a bit this and would liekly store the phone number as an
attribute for each user.

--
Patrice

"Miro" <mi**@beero.coma écrit dans le message de groupe de discussion :
ur**************@TK2MSFTNGP03.phx.gbl...

>"Steve Gerrard" <my********@comcast.netwrote in message
news:YK******************************@comcast.com ...

>>Miro wrote:
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.
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.

[Steve Gerrard]

Miro wrote:

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.

My cell phone does the same, and allows any number of numbers per contact. But
it does treat friends, family, business, and other contacts all as contacts.

I think you will find that the linker table doesn't help much, it just pushes
the same problem off to another table. Unless you mean a separate table of links
for each main table, i.e. a table of customer links, a table of vendor links,
etc.

Another option is to use 0 records, and have the phone number contain a
CustomerID, a VendorID, and however many IDs you have tables that need phone
numbers. By establishing a 0 record in Customers, you can then create an
enforced relationship of Customers to the CustomerID field of your phone record.
You just need to be sure to exclude the 0 record when you are retrieving a list
of Customers.

It may or may not work for your situation. I have used it to allow Note records
to be associated with records in several different tables, sometimes linked to
several at once. For example, a Note record can be linked to a contact, or to a
project, or to a quality issue, or to a specific product, and may be linked to
several of those if needed.

[Sam Hobbs]

"Miro" <mi**@beero.comwrote in message
news:Ow**************@TK2MSFTNGP02.phx.gbl...

>
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

>>century. Your requirements have been solved long before."

Wow computers can do that now?
Thanks for that great information.

My solution was to create 3 seperate tables.

M.
"Sam Hobbs" <sa****@social.rr.com_change_social_to_socalwrot e in message
news:%2****************@TK2MSFTNGP04.phx.gbl...

"Miro" <mi**@beero.comwrote in message
news:Ow**************@TK2MSFTNGP02.phx.gbl...

>>
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.