CODEWITHSUNDEEP
mysqlsqldatabase-design
RandomWhiteTrash
RandomWhiteTrash

Reputation: 4014

Database structure for packages library

Question. I have an item that can have an individual values or a 'package' values.

e.g.:

Item

Table Car
id INT
package_id INT
alloys BOOLEAN
aircon BOLLEAN
airbags TINYINT
transmission ENUM('auto','manual')

And packages:

Table Package
package_id INT
alloys BOOLEAN
aircon BOLLEAN
airbags TINYINT
transmission ENUM('auto','manual')

Customers can create any package (there is more fields than in this example, say 15) and then apply it to any car. Its a one to many relation (one package can be applied to many cars). However they dont have to use packages. They can specify all elements individually. Now my question is what is better to do?

  1. Like its done above and if package_id in Car is NULL then use values from Car
  2. Store every set of changes in Package even if it's not the package per se but a custom work (and add some is_package bool to determinie if it should show in packages list).
  3. Create separate table for storing those custom, one time options?
  4. Any other idea?

Thanks in advance!

Upvotes: 0

Views: 103

Answers (2)

pleasedontbelong
pleasedontbelong

Reputation: 20102

Basically you have TWO types of cars... custom cars, and cars that comes from a package.

It's a problem that could be solved using inheritance.

When you're modeling inheritance you have two options: - create a single table with all the attributes - create separate tables for each children

They both have their pro and cons but basically, separating them into different tables allows you to better preserve your data integrity when one of the child is related to different tables.

For example: when you see a car in the package_cars table, you are 100% sure that this car is related to a package and it doesn't have its own attributes. However, if you use a one single table, your table is more exposed to data inconsistency because you'll depend on rules like "if package_id in Car is NULL then use values from Car" and that can be only controlled in the business layer and not on the model.

To illustrate this, let's say that you add a table called "category" and the rule is: "customized cars have a category. But packaged cars don't, because the category is related to the package" If you use a single table you'd only need to add a category_id to the table, but now you'll need to remember a more complicated rule: "if package_id is NULL then use values from Car AND category_id should not be NULL". And if someone makes a mistake (why not?) and adds a category_id and a package_id to the same car, you'll have a data inconsistency.

In a few words, when children have different relations to the rest of the tables, its better to separate them into different tables to avoid using null-ables foreign keys they might be confusing and inconsistent.

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

if I understand correctly, you describe a simple 1:n relationship. I'd prefer 2nd option and setting Car.package_id as NOT NULL :

Table Car
---------
id INT PRIMARY KEY
package_id INT NOT NULL
FOREIGN KEY (package_id)
  REFERENCES Package (package_id)    

Table Package
-------------
package_id INT PRIMARY KEY
alloys BOOLEAN
aircon BOLLEAN
airbags TINYINT
transmission ENUM('auto','manual')

You will still be able to find - with a simple query - which packages are "custom" (used only in one car) or used by many cars.

Upvotes: 2

Related Questions