In a muddle (Relationships) (1 Viewer)

[ALANmac]

I have got myself into a mess regarding what should be a pretty simple setup, any pointers are appreciated.

1. Customers can have several products (type A or B)
2. The customer can be Husband and Wife (team) but may have a product together or separately
3. A team will be allocated a specific team number which is totally unrelated to the products
4. Products can be suplied by many suppliers
5. The Husband Wife normally live at the same address so don't want to enter that in twice.

At first glance it seemed straight forward but the more i think about it the more of a mess I got into
If anyone can advise a table relationship protocol that would be great.

 

[wazz]

1. Customers can have several products (type A or B)
2. The customer can be Husband and Wife (team) but may have a product together or separately
3. A team will be allocated a specific team number which is totally unrelated to the products
4. Products can be suplied by many suppliers
5. The Husband Wife normally live at the same address so don't want to enter that in twice.

a few ideas:

- tblCustomers: all individuals; forget about whether they are married, put all people here separately.
- tblTeam: ID=team number; one Team can have many (two maximum i suppose) Customers.
- tblSuppliers
- tblProducts
- tblAddresses: one address may have many (two max?) customers; if a customer can have many addresses, then relate the tables that way.

- the forms will be the great challenge, perhaps. leave those aside to begin with. after the tables make sense start working on the forms, on paper first.

hth.

 

[ALANmac]

Thanks, you have given me some ideas - I am going through this piece by piece at the moment.

 

[grnzbra]

CUSTOMERS table as above
TEAM table with team id and other info about team
TEAMCUSTOMER table linking customers and teams. Teams can have any number of customers and customers can be on any number of teams
SUPPLIERS table
PRODUCTS table
SUPPLIERSPRODUCTS table linking suppliers and products. Supplier could supply a number of products and a number of suppliers could supply the same product.
Address table
CUSTOMERSADDRESSES table linking Customers and Addresses. Multiple customers could be at one address and one customer could have multiple addresses
SUPPLIERSADDRESSES table. Similar to CUSTOMERSADDRESSES table.