By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 1,756 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Can't get past table strategy (that IS step one right?)

P: n/a
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

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!!)


Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies

P: n/a
Monkey-Wrench #1 Each "Product" can have several different (or same)
"Models" in addition to the models having different and/or same and
multiple "Components".

Nov 13 '05 #2

P: n/a
Let's start with your Customers - Products relationship.
Each Customer may be associated with one or more products, and each product
may be associated with one or more Customer.
This is a classic "many-to-many" relationship, and is implemented using a
third "join" table.
This table has at least 2 fields - one to link to the primary key of the
Customers table, and one to link to the primary key of the Products table.
These two fields together may constitute the primary key of the join table.
Each record describes a Customer - Product pair; if there is no record for a
given pair, that means that customer is not associated with that product.
You may want other fields in this table, for example date of purchase.
Or you may want to handle that elsewhere.

Once you've mastered this way of setting up relationships, you'll find that
it suits several of your other needs as well.

I'd suggest that you play around with this sort of structure for a while,
then come back with your next specific question.

- Turtle

"Detroit_Dan" <dm******> wrote in message
Monkey-Wrench #1 Each "Product" can have several different (or same)
"Models" in addition to the models having different and/or same and
multiple "Components".

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.