design - use two tables or one?

[c924b]

I'm tracking containers, which hold parts in a small assembly line,
these containers have a fixed ID attached to them and are used
over-n-over again, from workstation to workstation. Near the end of
the process the parts are transferred over to shipping containers,
which are tracked to a few more workstations in the same manner. The
shipping containers have unique ID's and will never be seen again.

My design question is should I use two different tables for each type
of container or should I use one table for both and assign a type
attribute (Container Type table)?

Example:

tbContainerType
ContainerType [text] - key

tbContainer
ContainerID [autonumber] - key; used when type is shipping
container.
ContainerType [foreign key]
ContainerNumber [long] - used when type is NOT a shipping container

Thanks

 

[John Nurick]

I think the key question is whether the two kinds of containers ever
"overlap" on the assembly line. E.g. if you have 10 workstations A-J,
and the transfer from the reusable in-plant containers to shipping
containers always takes place at workstation G, then the two different
kinds of containers are not only different physical objects but also
serve distinct functions and are best treated as separate entities.

On the other hand if the transfer may take place anywhere between F and
I, subclassing may be appropriate.

I'm tracking containers, which hold parts in a small assembly line,
these containers have a fixed ID attached to them and are used
over-n-over again, from workstation to workstation. Near the end of
the process the parts are transferred over to shipping containers,
which are tracked to a few more workstations in the same manner. The
shipping containers have unique ID's and will never be seen again.

My design question is should I use two different tables for each type
of container or should I use one table for both and assign a type
attribute (Container Type table)?

Example:

tbContainerType
ContainerType [text] - key

tbContainer
ContainerID [autonumber] - key; used when type is shipping
container.
ContainerType [foreign key]
ContainerNumber [long] - used when type is NOT a shipping container

Thanks

 

[c924b]

Thanks John,

Clearly the biggest difference is one is used for shipping and the
other is used for inter-plant transportation, but the tracking from one
workstation to the other is no different and I was trying to reduce
duplicate work to handle the tracking. I was looking to handle the
other information needed shipping in another table altogether (which is
like your suggestion of another table) in a one-to-one relationship. -
So maybe I'll be using both of your suggestion. - Thanks again John!

I think the key question is whether the two kinds of containers ever
"overlap" on the assembly line. E.g. if you have 10 workstations A-J,
and the transfer from the reusable in-plant containers to shipping
containers always takes place at workstation G, then the two different
kinds of containers are not only different physical objects but also
serve distinct functions and are best treated as separate entities.

On the other hand if the transfer may take place anywhere between F and
I, subclassing may be appropriate.

I'm tracking containers, which hold parts in a small assembly line,
these containers have a fixed ID attached to them and are used
over-n-over again, from workstation to workstation. Near the end of
the process the parts are transferred over to shipping containers,
which are tracked to a few more workstations in the same manner. The
shipping containers have unique ID's and will never be seen again.

My design question is should I use two different tables for each type
of container or should I use one table for both and assign a type
attribute (Container Type table)?

Example:

tbContainerType
ContainerType [text] - key

tbContainer
ContainerID [autonumber] - key; used when type is shipping
container.
ContainerType [foreign key]
ContainerNumber [long] - used when type is NOT a shipping container

Thanks

 

[mnature]

One other consideration could be whether you will ever track which parts
container contents went into which shipping container, which might be done
for quality control reasons. If you might ever do that, then using two
tables would allow that more easily than if you placed all containers into
the same table. Sounds like you don't do that, but thought I would mention
it for the sake of completeness.