Wednesday, March 7, 2012

database design??

Hello,

I am designing my first database with 5 tables for a demo project and am not sure if it works. an example below.

2 of the many things I want visitors to the site to do is find a company by the industry sector they belong to,..and

what sort of service or products they can supply. For instance a Employment agency maybe under professional services

Table 1 Customer

Customer_ID = primary key,,,, Sector_ID = Foreign key

Comapany Name, Address, Phone, Postcode etc

Tabel 2 Industry Sectors

Sector_ID = primary key,,,,Customer_ID= foreign key

banking, Education,Prof Services, etc

Table 3 Trading Activity

Trading_ID = primary key,,,,Sector_ID = Foreign key, Products_ID= Fk

Employment Agent, School, Lawyer etc

Table 4 Products

Products_ID = primary key,,,,Trading_ID = foreign key

Supply frozen foods, transport services, sports goods, etc

Table 5 Account

Account_ID = primary key,,,,Customer_ID = foreign key

Account Name, Credit Limit, Payment Terms, Open date, Account contact etc

One big point of confusion is, can I have the Customer_ID from the principal Customers table

in every table as a foreign key or must the tables be chained together one after the other as such.

Advice appreciated

Thanks

Hi

There are some problems in your design.First thers are some general rules you should keep in mind.

For 2 objects A B, if there are 1-many relation between A and B, primary key of A should be inculded as foreign key in B .

if there are many-1 relation between A and B, primary key of B should be inculded as foreign key in A .

if there are many-many relation between A and B, you need to create a new table A-B,primary key of B and A should be inculded in the new table

if there are 1-1 relation between A and B, Columns of A and B could be included in a single table .

For table1 and table2 if one customer belongs to many industry sectors and one industry sectors have many customers ,then you should create a new table with primary key of talbe1 and table2 inculded. Table 3 and talbe 4 are the same.

If I misunderstand your meaning ,pls tell me .

No comments:

Post a Comment