RandomWhiteTrash RandomWhiteTrash - 4 months ago 11
MySQL Question

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!

Answer

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.

Comments