Need help with new table structure

[Guest]

I am in the process of "normalizing" my database. I need to have a list of
Owners, but it is possible that I will have 5 different contacts within each
owner and/or and Owners name (ABC Corp.) with no contact name. I would
like them to be listed as separate contacts. Is that the best solution?

 

[Rick B]

TblCompany
CompanyNumber
CompanyName
CompanyAddress1
CompanyAddress2
CompanyCity
CompanyState
CompanyZip
CompanyMainPhone
CompanyMainFax
CompanyWebSite
etc...

TblContacts
ContactNumber
CompanyNumber (links to previous table)
ContactFirstName
ContactLastName
ContactDirectPhone
ContactCellPhone
ContactEmail
etc...


Each company would have one record in the TblCompany and as few as no
entries in the Contacts table or many entries.

 

[Guest]

Thanks much!!

TblCompany
CompanyNumber
CompanyName
CompanyAddress1
CompanyAddress2
CompanyCity
CompanyState
CompanyZip
CompanyMainPhone
CompanyMainFax
CompanyWebSite
etc...

TblContacts
ContactNumber
CompanyNumber (links to previous table)
ContactFirstName
ContactLastName
ContactDirectPhone
ContactCellPhone
ContactEmail
etc...


Each company would have one record in the TblCompany and as few as no
entries in the Contacts table or many entries.

 

[Guest]

Ok - what if I have 2 or 3 different address for one owner and 2 contacts at
each address. What would be the best thing to do in that case?

 

[Guest]

Then re-arrange your address info into the contact table. Address 1 for
ordering, 2 payment, 3 billing, 4 delivery, etc.

 

[PC Datasheet]

You need the following tables:

TblOwner
OwnerID
OwnerName
etc

TblContact
ContactID
OwnerID
ContactName
etc

Use a form/subform to enter owners and contacts. Enter owners in the main
form and contacts in the subform.

 

[John]

Then you need 2 one to many relationships.


tblCompany
-------------
pkCompanyID Autonumber primary key for table
CompanyName


tblAddress
------------
pkAddressID Autonumber primary key for table
Address1
Address2
PhoneNumber
FaxNumber
etc
fkCompanyID number (long integer) foreign key to pkCompanyID



tblContacts
------------
pkContactID Autonumber primarykey for table
ContactName
Position
MobilePhone
fkAddressID number (long integer) foreign key to pkAddressID

This way you can have several addresses for each Company and several
contacts for each address. Depending on what you think will be necessary you
can sub-divide again. For example if you thought there might be several
phone numbers or email addresses for each address you can create a separate
email and phone table and relate them to the address table.


John

 

[Marc]

Then

TblAddress
AddressId
AddressType
CompanyNumber(link)

HTH
Marc