How Many Tables is Too Many? (1 Viewer)

[sambo]

Design Idea:
I have been given the task of tracking the progress of every Serial Numbered Unit that enters the building. Progress Updates will be made by Repair workers as they complete any particular phase of the Repair.
Question:
Since I need to track multiple events to each particular unit, should I give each unit its own dynamically created table? We usually handle about 80 returns at any given time. This means a WHOLE LOT of tables. But, info will be easily accessible and easy to put into Reports.
Suggestions Would Be Appreciated

 

[Rich_Lovina]

If you have 10,000 serial nos. then these go in one table. Second table will be common features for each unit, and the units will be records in the unit table. Think of it like personnel in a department and again, study the Northwinds sample.

You create new tables in your query, even if you want to see how 20 units received 500 serial numbered jobs/parts last week.
So, no, each unit will not be a separate table, but a separate record within a unit table.

Hope this helps.

 

[sambo]

Here is an example of what may happen to any given serial numbered unit.

SerialNum: 55655
History:
1. Recieved by shipping
2. Taken to returns
3. Recieved by returns
4. Inspected by returns coodinator
5. Repaired
6. Checked out of returns
7. Recieved by shipping
8. Shipped

Each unit could have different history paths. How will I trace these paths and be able to form reports without giving them their own table.

Help..

 

[RichMorrison]

You wrote
<<
How will I trace these paths and be able to form reports without giving them their own table.
>>

Like this:

1) Make ONE table that represents each Part. It looks like the Serial Number would be a primary key. For each Part there is a row in this table.

2) Make ONE table that represents "Activity" applied to a Part. This table has a non-unique key that consists of the Serial Number and a sequential Activity Number. For each Acticvity applied to a Part, there is one row in this table.

When you get a part in, you add it to the Part table. As you perform various activities on a Part, you add each to the Activity table.

When want to report on a Part, you run a query that selects all the Activities for a Part. From this query you can build reports that show all that has been done to a Part.

Of course there will be more detail for you to record for Parts and Activities.

But this is the most sensible database design.

RichM

 

[Rich_Lovina]

That's a great, logical explanation!

 

[sambo]

Every Uniquely identified unit will have not 1, BUT 2 features in common. Each has serial number is given a unique repair number.
SO, in a sense, do i now have a combination primary key...

Here is a look at the table set up

SerNum | ReturnNum | DateMod | Location
| |
| |
| |
| |
|
|
|
One to One

Does this give me a duel primary key, if so, how do I do that?

 

[RichMorrison]

The "Serial Number" is either:
unique
OR
not unique

If unique then it is the primary key.
If not unique, then you need an additional field or fields.

RichM