help with tricky one to many relationship please

[Doc]

A simplified version of this problem for a picture framers is as
follows:
One customer makes many orders.
One order can be EITHER for a new picture frame - needing fields like
size, wood used, glasing etc OR for a repair - needing fields like
fault, damage report, materials used.

PK in customers is cust ID
PH in orders is orderID

This should be a simple three table design. one cust makes many orders,
one order contains many order lines

Qn, how do I implement the order lines when the number and type of
fields required is different for sales and repairs?
Doc

 

[Craig Alexander Morrison]

The standard approach would be to have an OrderLine table which contains all
the fields that are consistent with both Sales and Repairs and then have a
pair of 1 to 1 relationships with the OrderLine with a table called say
OrderLineSale and one called OrderLineRepair in this you would put the data
that was required for that type of orderline only.

I would recommend that you search for "Sub Typing" , "Sub Classing" and "1
to 1 (one to one)" or "Type Hierarchy" on google for more details.

 

[mnature]

Interesting problem. If repairs were always associated with a previous
order, then you could create a third table that comes off of your order
table, that would then include all of the relevant fields for a repair, but
be associated back to the original order that the repair is based on.
Sometimes, though, you might repair something that is not related to your
work. In this case, you could just make a note in a new order that you are
doing repair work, and use the repair table to tabulate what you have done.
The nice part of having a repair table that relates directly to the order
table, is that it can be as a 1 to many relationship, so that if you need to
do multiple repairs to the same original order, that would be built in to the
relationship. Could be handy, for doing quality control of your shop.

 

[Howard]

Thank you both.
What you suggest is correct, repairs are not always on original orders but
exist as separate entities and yes, many repais may be done on the same
order, just as many standard frame types may be purchaced on one order. In
addition I will be building stats reports broken down by repairs or sales so
I do need to know the difference.

Qn for mnature though.

Exactly what do you mean by 'make a note' in the order table. If I have a
field there, say 'RepairID' linking it to the repair table and another , say
SalesID' linking it to the sales table then one or the other will always be
null. Surely this breaks some cardinal rule or other?

Doc

PS
II suppose it would be feasible to have two separate relationships, one
customer table, two order tables each related to either the repairs or the
sales but then doing things like finding all income from one customer or
making one bill for a customer is tricky!

 

[mnature]

I was thinking of using three tables:

tblCustomer
CustomerID (PK)
CustomerName

tblOrder
OrderID (PK)
CustomerID

tblRepair
RepairID (PK)
OrderID

Orders would always be associated with a customer, and repairs would always
be associated with an order. You would not necessarily always have a repair
associated with an order, but you could have several repairs associated with
an order. That is why I mentioned a note field, where you could state that
an order is simply a repair, which is not associated with your work on a
previous order.

This is, of course, just a bare bones of the tables, because you would also
have fields to detail supplies used, finished sizes, etc. Supplies could
even be in a fourth table, as frameworks, mattings, different types of glass.
It is possible to get a little carried away with the details that can be
compiled in various tables. For instance, you could have tables that just
detail the different frameworks that can be used. Or, you can have a table
with all the different supplies, but with a field that specifies the type of
supply that it is.

You could have a field in your orders table that will just be for detailing
the type of framework, then have two more fields that will be the height and
width of that framework. Or have a separate table that draws from a
materials table and the orders table to detail what you are using in an
order. Or you can just have a large memo field where all of that is typed
in. That is the really tough part about setting up a database, is deciding
what you really want and need as fields.

Another consideration is what you will do with all of this information. If
you only need to know the specifics of an order while you are filling the
order, then a large memo field for the description is probably all you need.
If you want to do statistical analysis or materials tracking, then you may
need to make fields a little more specific for tracking that sort of
information.

Taking a little time now to consider such things will make your database
much more useful for what you need it for. It might also cause you to
consider purchasing a "canned" program for doing some of this (or download a
template).

Good luck with your project.