Schema Design for a Inventory / BoM DB
Posted by: Saad Abbasi
Date: September 27, 2014 04:40AM

I have the above tables in a database I'm designing right now, in MySQL. The primary purpose of the database is to create Bill Of Materials for a database and enforce revision control on these Bill Of Materials.

The Parts table follows Single Table Inheritance and has 3 different types of parts: Connectors, Terminals, Seals.

Brief Description Each Type

**Connector**: These are automotive grade connectors used in the manufacturing of automotive wiring harnesses.

**Terminals**: A connector has crimped wires inserted into it. A Terminal is crimped onto a wire in order to create a solder-less joint. These terminals then mate with their counterparts when the connecter is mated with it's counterpart in a vehicle.

**Seals**: These are special type of seals that are inserted onto the the wire in order to prevent water/dust getting through to the interconnection.

A connector can be used with multiple types of terminal and a terminal can also be used with multiple types of connectors.

The relationship between a connector and a seal is similar. A seal and terminal have no relationship.

What I'm aiming for:

1. If the user is browsing some part, I would like the view to show all it's related/associated parts. For instance, if Connector id 1 can be used with 5 different types of terminals, I would like all these terminals be shown in the view.

2. Similarly, when Terminal is being viewed, I would like all the different connectors that it can be used with shown as well.

3. Furthermore, a Connector can have substitute parts and I would like to relate that as well. This is a one-to-many relationship as a connector can have multiple substitutes.

4. And finally, a Connector can have multiple counter-parts and I would like these to be related as well.

I'm new to database design and I'm having trouble seeing the forest through the trees. Personally, I think I should ditch the Single Table idea and go with separate tables for Connectors, Seals and Terminals and draw up relationships between them.

That still answer how I can show substitutes and counter-part connectors.

Options: ReplyQuote


Subject
Written By
Posted
Schema Design for a Inventory / BoM DB
September 27, 2014 04:40AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.