Very basic Contacts table design question 1

[pbsandrad]

What is the best table design to use when you have several different business categories (i.e. banks, insurance companies, manufacturers, charity foundations) and several different contacts at each company. Should I break the tables down into business categories and then further break them down into a contact table for each category?
So i'm thinking as an example I would have tblBanks with the primary key being companyID (B001) and then tblBanksContacts with the primary key being combined (companyID, contactName. Suggestions would be greatly appreciated. Thanks

 

[traingamer]

It sounds like two main tables to me. That simplifies EVERYTHING. If you just want bank contacts, then select Company with type = 'bank' or (bank code).

Company:
companyId
CompanyName
CompanyType 'bank, non-profit,- could be code from table
CompanyAddress
etc.

Contact:
ContactId
companyid
Name
etc.

That way a small number of queries and reports will cover all possibilities.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[willir]

Can a corporation belong to more than one category? For example, a bank can also offer insurence. If so, then you have a many-to-many relationship. If not, then the design is more simple...

With a M:M...

tblCompany
CompanyCode - primary key
CompanyName
etc

tblBusCat
BusCatCode - primary key
CatName

tblCompanyCat
CompanyCode - foreign key to tblCompany
BusCatCode - foreign key to tblBusCat

If not, then you can store the business category on the Company table and not use the tblCompanyCat

tblCompany
CompanyCode - primary key
CompanyName
BusCatCode
etc

Richard