Table normalization

[Guest]

I want to restructure our database and have some general table/normalization
questions. W manufacture beds and associated cabinetry a typical invoice may
look like

#221 double bed styleV with 2 24" cabinets

(of course there are associated details with the choice of bed and cabinets
not included in the sample due to simplification)

Since there are some slight differences in the data collected for the beds
and cabinets would it be better to set up one table that may contain empty
fields like this

tblOrders
----------------------------
record# 1
customerID 12
invoice# 221
component Queen Bed
Style V
componentwidth [null]

record# 2
customerID 12
invoice# 221
component Cabinet
Style V
componentwidth 24

....or develop seperate tables for the beds and cabinets which may not have
empty fields

tblBedDetail
----------------------
record# 1
custID 12
invoice# 221
BedSize Queen
Style V

tblComponentDetail
--------------------------
record# 1
custID 12
invoice# 221
component Cabinet
style V
width 24

 

[Allen Browne]

Ultimately you are closer to your data than we are, so you will have to
decide, but here's a choice you might consider.

It is possible that some products might consist of combinations of other
products? For example, you might sell a headboard as a product on its own,
bedside tables on their own, and also a product that consists of a bed,
headboard and pair of tables?

If so, there is a one-to-many relation between Product and the products in
that product.

ProductInProduct table:
MainProductID the product that contains others.
SubProductID the product in this product
Quantity how many of this sub in the main.

So, if product 53 consists of a bed (product 21), headboard (product 22) and
a pair of bedside tables (product 23), you would have these records in the
table:
53 21 1
53 22 1
53 23 2

To create the relation, add a 2nd copy of the Product table to the
Relationships window (Tools menu.) Access aliases it as Product_1. You can
then relate Product_1.ProductID to ProductInProduct.SubProductID.

The advantage of this approach is that all products (stand-alones and kits)
are all in the one table. So now you can have a ProductID field in your
OrderDetail table with only one table to refer to.

BTW, you won't put a CustID into the Product table. It goes into the Order
table. To see how to connect customers to orders, order details to orders,
and products to order details, open the Northwind sample database that
installs with Access. Choose Relationships on the Tools menu to get the
overview of how the tables connect.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I want to restructure our database and have some general
table/normalization
questions. W manufacture beds and associated cabinetry a typical invoice
may
look like

#221 double bed styleV with 2 24" cabinets

(of course there are associated details with the choice of bed and
cabinets
not included in the sample due to simplification)

Since there are some slight differences in the data collected for the beds
and cabinets would it be better to set up one table that may contain empty
fields like this

tblOrders
----------------------------
record# 1
customerID 12
invoice# 221
component Queen Bed
Style V
componentwidth [null]

record# 2
customerID 12
invoice# 221
component Cabinet
Style V
componentwidth 24

...or develop seperate tables for the beds and cabinets which may not have
empty fields

tblBedDetail
----------------------
record# 1
custID 12
invoice# 221
BedSize Queen
Style V

tblComponentDetail
--------------------------
record# 1
custID 12
invoice# 221
component Cabinet
style V
width 24