I'm a first year web development student and I'm looking for some advice on a MySQL database design in order to expand a website I recently created for an assignment.
The website I'm currently developing will serve as an online database of analog photography equipment. At present it allow users to search or use filters to find particular cameras on the database. Each camera has it's own page on the site that displays information and a picture etc, and ideally I would like this information to be editable by registered users, like a wiki.
Current Database design:
Currently, the database design for camera data looks like this:
--> Current database design .png
You can see in the diagram that I have a 'cameras' table containing all the cameras with basic information. Then, for sake of normalization and filtering purposes, I have additional tables for all the different 'camera types' that exist and all the 'camera brands' that the cameras in my database belong to.
Now what I would like to do is expand my database to be able to store technical specifications such as lens, flash, shutter speeds etc for each camera, but I realise that the relevance of some of this information is dependant on the camera type (for example compact cameras have fixed lenses but SLRs have variable lens mounts) and ideally I would only like to display information on the cameras page that is relevant to it's camera type.
-- Database Re-design --
What I was thinking was that I could store a camera's information over two tables - keep the existing cameras table with general camera data (like name and year released), but also create additional tables on my database for every camera type (SLR, TLR, compact, instant ...) which would each store tech specs that are relevant to that camera type and use the 'camera_ID' from the 'cameras' table as a foreign key to link a camera's data between the two tables.
Although I am unsure whether this is the best solution or not - Perhaps it's too messy and over-complicated to have camera data spread over two tables? I also wondered whether this would cause issues later on if a user decided they wanted to change a camera's type because that camera record would then have to be moved to a different table and have it's existing data consolidate into it. Also if some camera types have the same variable attributes I would be duplicating columns across different camera type tables, which I'm not sure is efficient.
The only other way I could think of was to maintain the existing design and just add a whole lot more columns to the 'cameras' table for all the possible technical specifications that could apply, but that just means it might be a little inefficient as there would be many columns that may not be relevant to each and every camera and would remain empty (although maybe this isn't such a big issue??)
Sorry if that's a lot of info to take in, but I'm a little stumped on it! Any advice is greatly appreciated. Thanks in advance :)
You are describing a form of inheritance. Unfortunately, current relational database management systems don't support inheritance directly, so it has to be emulated - the two solutions you proposed are known as "table per class" and "table per (whole) hierarchy".1
If you have a large number of classes (or anticipate a need to add new classes in the future), go for the former2, otherwise you can use the later.
For more on inheritance (aka. category, subtyping, subclassing, generalization hierarchy etc.), take a look at ERwin Methods Guide, "Subtype Relationships" section. Also, take a look at this post.
1 There is also a third strategy, where every child class is represented by a table, but parent class doesn't have a table of its own.
2 Fields and constraints (such as foreign keys) that are common you can keep in the parent table, so they don't need to be repeated in the child tables.
