CODEWITHSUNDEEP
databasedatabase-design
Álvaro García
Álvaro García

Reputation: 19396

is this a good design of the database?

I have four tables. Manufactures, ProductionPlants, ProducitonLine and Machines. Machines are the machines that are used to manufacture some prduct.

A manufacture can have many plants and a plant can have many production lines can have many machines. So all the relationships are 1:N.

So the tables have the following fields:

Manufactures      (IDManufacture,...)
ProductionPlant   (IDProductionPlant, IDManufacture,...)
ProductionLine    (IDProductionLine, IDPlant,...)
Machines          (IDMachine, IDProductionLine,...)

However, this makes that a machine must be installed always in a line fo production, but sometimes a machine can be uninstalled, not used, but still is property of a manufacture.

In the disign, if I set null the IDProductionLine field in the machine table, then I don't be able to know the manufacture that has this machine.

SO the first solution is to add other field to the Machines table, the IDManufacture, so I can set null IDProductionLine but still can know the owner.

But my doubt if this is a good solution, because this create a cycle in the relationships, because from Machines, I can know the manufacture by the IDMachine or by the LineProducion-->PlantProduction. is this a problem or is a good solution?

When to elimiate cycles and when is admissible to have a cycle?

Thanks.

Upvotes: 2

Views: 81

Answers (1)

cchamberlain
cchamberlain

Reputation: 17964

I would decouple Machines (Machine inventory in a specific product line) from your types of machines to make this more normalized -

Manufacturer
 1:N
Prod Plant
 1:N
Prod Line
 1:N
Machine Inventory
 N:1
Machine Type

The machine type table would store 1 row for every type of machine used at the company, and machine inventory would contain unique information for every instance of a machine used in a specific product line. With this structure you would be able to remove a machine from a product line, and still maintain details about the machine in your database. There would also be less redundancy with this structure should you be using multiple instances of the same machine or using the same type of machine in different product lines or plants as all those machine inventory rows would point back to the machine type.

Upvotes: 1

Related Questions