Pourmoi
I see the light....
Thank yu for providing specifics.
Can I / may I present your database with a slightly different slant?
You have customers (companies).
They place (sales) orders.
The order can include different items.
A typical way to approach this is to have four main tables -- one for each these "things" / "nouns" / "entities".
tblCompany
CustomerCode - text, primary key
CustomerName - text
Address - text
PrimaryPhone - text
Discussion:
These are your customers. More on this later, but include things unique to the Company. Some things to tweak later include the address and contact info.
tblProduct
ProductCode - text, primary key
ProductName - text
CurrentPrice - currency
ProductUnits - text
Active - yes / no boolean
Discussion:
A product or item table will depend greatly on what you sell. If you are selling wine, cars, insurence or a service will influence the fields used to capture the data. For example units of liters or gallons, pounds or kilograms, hours, etc -- 1000 L of wine, 1000 bottles of wine, 1000 kg of butter, 40 hrs of service or one car. The other thing is inventory in stock / on-hand. Measuring inventory is a huge discussion item. So let's keep this simple for now.
tblOrder
OrderID - primary key
CompanyCode - foreign key to tblCompany
OrderDate
ShipToAddress
ContactName
CustomerRef
SpecialInstructions - memo
tblOrderDetail
OrderDetailID - primary key
OrderID - foreign key to tblOrder
ProductCode - foreign key to tblProduct
OrderQuantity - depends (interger, single...)
ItemPrice - currency
Discussion:
Again this will depend on what you sell. And you may state, well I sell cars so why do I need two tables to describe how I sell cars?? The answer is that using two tables gives you much more flexibility. With one table, you may sell a car for $20,000 and then use a huge memo field describing what the "car" includes. OR you can itemize what was sold - 1 car - $15,000, freight - $1,000, tires - $500, accessories - $2000 and taxes and discount making up the remainder.
...Moving on
In your example, TableA and TableB, you really have described the tblOrder and tblOrderDetail. Rather than "copying" data in a one-to-one relationship, you really just need to "link" one table to the other.
Instead of typing in the Company info for the tblOrder / TableA, you just need to link to tblCompany using the CompanyCode. Perhaps some test data may help -- you are selling "pens" and "stationary"
[tt]
tblCompany
CompanyCode CompanyName
CallMack Call Mack Plumbers
1stRate First Rate Builders
Maple Maple Leaf Reastaurant
tblProduct
ProductCode ProductName CurrentPrice ProductUnits
PEN_BL_EC Economy Blue Pen 1.00 each
PEN_RD_GL Gel Red Pen 3.00 each
PPR_WT_CR White paper 10.00 box
PPR_BG_PK Beige paper 5.00 package
tblOrder
OrderID CompanyCode OrderDate ...
1 CallMack 05/01/2005
2 1stRate 05/03/2005
3 Maple 05/10/2005
tblOrderDetail
OrderDetailID OrderID ProductCode OrderQuantity ItemPrice
1 1 PEN_BL_EC 5 0.90
2 1 PPR_WT_CR 2 9.00
3 2 PEN_BL_EC 10 0.85
4 2 PEN_RD_GL 5 2.85
5 2 PPR_WT_CR 2 8.00
6 2 PPR_BG_PK 5 4.00
7 3 PEN_BL_EC 10 0.90
8 3 PPR_WT_CR 5 9.50
9 3 PEN_RD_GL 3 3.00
[/tt]
Looks meaningless?
CallMack bought 5 blue pens at $0.90 each plus 2 cartons of white paper at $9.00 on May 1, 2005.
The tblOrderDetail contains links to the tblOrder and the tblProduct. Although prices in tblProduct are provided, they have been over-ridden in this example. The one thing is "how much" did CallMack pay in total?
With relational database, calculated values are normally not stored. However, since these values will not change, and we are dealing with money, storing the extended price is a common practice...
tblOrderDetail
OrderDetailID - primary key
OrderID - foreign key to tblOrder
ProductCode - foreign key to tblProduct
OrderQuantity - depends (interger, single...)
ItemPrice - currency
ExtendedPrice - currency.
Notice anything unusual about the sample data?? Sales tax -- assume 5% (is that too much??)...
[tt]
tblOrderDetail
OrderDetailID OrderID ProductCode OrderQuantity ItemPrice ExtendedPrice
1 1 PEN_BL_EC 5 0.90 4.50
2 1 PPR_WT_CR 2 9.00 18.00
3 1 SalesTax 1.13
4 2 PEN_BL_EC 10 0.85 8.50
5 2 PEN_RD_GL 5 2.85 14.25
6 2 PPR_WT_CR 2 8.00 16.00
7 2 PPR_BG_PK 5 4.00 20.00
8 2 SalesTax 2.94
9 3 PEN_BL_EC 10 0.90 9.00
10 3 PPR_WT_CR 5 9.50 47.50
11 3 PEN_RD_GL 3 3.00 9.00
12 3 SalesTax 3.28
[/tt]
...Moving on
In your TableB, you have two "fields" that help in describing the "things" or "entities". "Color" describes the item being sold, and "mode of payment" decribes the terms of the sale.
Pop quizz. Where would color go -- tblProduct, tblOrder or tblOrderDetail? It could go on either tblProduct or tblOrderDetail. If the color is described in tblProduct, then it does not have to be descirbed in tblOrderDetail. And if it is part of tblOrderDetail, then it is not required in tblProduct. In the above example, selling "pens" and "paper", I made the colour part of tblProduct (indirectly). Since I "buy" blue pens and "sell" blue pens, this makes a lot of sense. If I were to manufacture pens, then I could reference the colour in either tbale.
What about mode of payment? Only one answer here -- on tblOrder. This table describes the order, and how and order is paid is part of the description. I would not list each item in the OrderDetail table as being paid by credit card, or stipulate that all orders are sold by chque in the Company table.
On May 27th, I gave you some links to some pretty good references. This current post incorporates the ideas of design. For "how to" create references please consider reviewing Defining Relationships Between tables. To get you started, from the menu, "Tools" -> "Relationships". Add your tables. Then click on the "primary key" in one table and drag it to the "foreign key" on the related table. At the popup where you can further define the relationship, I recommend that you "Enforce referential integrity".
Richard
