Howdy all,
I am having trouble deciding on table strategies. My last dB project
was 1994ish in 1-2-3...
My goal is a database which will keep track of equipment in the field,
and provide rebuild parts lists based on a standardized rebuild set
with options selected at report time. I need to be able to add new
"Customers", "Products", "Components", and "Parts" over time.
I've decided that I have (of course) a customer table. Each customer
will (likely) have more than one "Product", though several customers
could have the same "Product" [Product is ABC machine]. Each
"Customers" "Product" is serialized uniquely.
Each "Product" can / will have several different "Components", and in
some cases, two or more of the same "Components".
Each "Component" will have an associated parts list. I would like this
parts list have checkboxes (just boolean column) associated with each
part to indicate that it is part of the overhaul. There will be a
default set, and then when the rebuild parts list is generated, the
operator will have the ability to select additional checkboxes [parts].
This will create uniqueness specific to "Customer" & "Product".
I would like to keep each customers overhaul parts list unique as some
want / need deeper overhauling than others. There are parts which only
get replaced every 2nd or 3rd overhaul also, but this is subjective;
hence the ability to change the standardized list at report generation
time.
Currently I have "Products" in their own table, and have a total of 11.
Currently I have a "Models" table which lists 38 different models.
These 38 are primarily two "Product" lines (of the 11, though this will
grow). I have a "Components" table which list 36 different components
(primarily applicable to only one of the "Product" lines, but this is
where I started second guessing my table organization.) I expect the
"Components" table to grow to about 150. I have yet to create the
"Parts" table (which will be LARGE like 2000??); one part may be
applicable to 80% of the components, and other parts may only be
applicable to one "Product".
I am concerned about several things.
1st I have yet to identify where each customer is associated with the
serialzed product. This serialized product will then have a model and
a bunch of components associated with it. Then each component will be
associated with its own parts list (which will be reviewed manually and
the special circumstance parts will be checked / unchecked). This is
where I would like to maintain the history of uniqueness for each
serialized product.
2nd Not all "Products" will be associated with a component, but rather
directly to certain "parts". I may be looking at that relationship
backwards where-as some parts may not be associated to a component but
rather to a product...
3rd Although I think this is probably easy once the tables are
arranged properly, The ultimate goal will have a customer select
screen. Once selected, this screen will show all "Products" that
customer has and allow for selection of which "Serialized Product" to
generate the overhaul parts list for.
And now I have written a book... Any and all help is appreciated.
(Psychiatric especially!!)
Dan
