Database Table Design Question??

[WestSide2003]

Hi,

I wanted to see if I could get some opinions on this.

I am designing a db. I have 2 types of companies (Clients and Suppliers) that I am going to be storing information on in the database.

Does it make sense to have two tables like,

tbl_Suppliers and tbl_Clients

OR

1 table called Companies and then have a field in that table called CompanyType where I would record whether the Company was a Supplier or a Client.

Keeping in mind there are slight differences between a supplier and a client as well as some similarities.

Any thoughts?

-WestSide

 

[SemperFiDownUnda]

Depending on the situation I might use 1 table. Basically if I could come up with a situation if a there is EVER a chance that a company could be customer and a supplier at the same time. This is very common these days where companies partner. Your partner is neither just a customer or a supplier but you work together to augment eachothers business.

Otherwise If I was sure that wouldn't happen I'd put them in 2 tables....and even in the above situation I'd still have a 2 key table indicating what they where but 1 repository for company information.

 

[SemperFiDownUnda]

Sorrry premature submiting problem
Suppliers and Customers are UNIQUE Foriegn Keys
I might incorporate the Specific data tables into Suppliers and Customers depending on the situation.

--------------
|Suppliers   |    ------------------------
 ------------     |Common Company Data   |
|FK_CompanyID|     ----------------------
--------------    |PK_CompanyID          |
                  |.                     |
                  |.                     |
--------------    |.                     |
|Customers   |    ------------------------
 ------------                             
|FK_CompanyID|    ------------------------
--------------    |Supplier Specific Data|
                   ----------------------
                  |FK_CompanyID          |
                  |.                     |
                  |.                     |
                  |.                     |
                  ------------------------

                  ------------------------
                  |Customer Specific Data|
                   ----------------------
                  |FK_CompanyID          |
                  |.                     |
                  |.                     |
                  |.                     |
                  ------------------------

 

[Craig0201]

Possible but there is no specific need for this relationship. You could have all the columns in a simgle table and allow nulls with flags for suppliers and customers.

Craig

 

[CajunCenturion]

It would be highly unlikely that you could have one table with both Supplier and Customers in the same table, and still adhere to the Full Functional Requirements of second normal form.

Are the similarities between the two simply coincidental, or is there some logical connection between the similar fields. Whereas it is true that Suppliers and Customers both have addresses, that type of similarity, IMHO, is not one which has any logical foundation upon which to base database design decisions.

Secondly why combine two different types of entities in the same table. Keep it simple - have a table for Suppliers, and have a table for Customers.

If there is a relationship between Suppliers and Customers, most likely many-to-many if so, then create a third table to identify the connections between the two.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[WestSide2003]

Hi,

My issue is:

1. A company could be a customer and a supplier
2. There are similarities between Suppliers" and "Clients" such as both have Addresses

Below is my initial field list for the "Clients Table"

ClientID (PK)
ClientType
ClientName
ClientSize
ClientIndustry
ClientYrsBusiness
ClientDBNo
ClientWebSite
ClientAddress1
ClientAddress2
ClientCity
ClientState
ClientZip
ClientMainPhone
ClientFax
ClientReferredBy
SendClientNewsletterA
SendClientNewsletterB
ClientBusinessBefore (yes/no)

My supplier table is pretty much the same thing. I may look to store 2-3 additional fields that might be different in each table.

I am also faced with the fact that I have multiple contacts at each Supplier and at each Customer. I am not sure how to go about that. I have been told to Create a separate table called "Contacts" and store them there...

Any further thoughts appreciated..

-WestSide

 

[lespaul]

Per Contacts: You should create another table for contacts. Include as a foreign key the SupplierID that way you can gather all contacts for each supplier:

Contacts Table:
ContactID (I believe in always having a unique ID)
SupplierID
ContactFirstName
ContactLastName (ALWAYS split into first/last!!)
ContactPhone
ContactEmail

Looking at your field list for Clients - suggestion:
Create an INDUSTRY table (if you want standardized industry selections in the Client table) then store the IndustryID in the ClientTable.

The 2 newsletter fields, why not combine to one field: NEWSLETTER - if NULL then no newsletter sent, if A then send Newsletter A, if B then send Newsletter B;

Who refers clients? Suppliers? then store the supplierId, employees? store the employee ID, various? then leave it as a text field for fill in but be aware that inconsistencies in Data Entry may result in unreliable data (for example if one person enters "General Mills" and another enters "GM" you won't be able to run a report that shows all the clients referred by General Mills since some of them are "GM&quot.

Exactly what type of database are you designing?






Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!