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