Database structure - Is this a correct way to do things?

Question

I have a table called production. This table will data related to movies, tv series, documentaries and anime, that way I won't have to create a table for each type of production. But it generated a problem. Since a tv series also can be a documentary, I will have to use a junction table that defines what kind of production we are talking about. I will paste my structure below.

     Table I : production

     production_id (Auto Increment & Unique)
     production_start_date (Movies won't get any value because start_date is only valid for Tv Series, so some columns will be empty in this table.)
     production_name
     production_end_date (See below)
     production_season_number (see below)

     Table II : types

     type_id (Auto Increment & Unique)
     type_value (This column will store only four values)

     Table III : production_type

     production_id (Foreign Key From production table)
     type_id (Foreign Key from types table)

Is this a correct way to structure a database for this particular purpose? Please be specific and as cruel as possible. :)

Answer 1

If a production can be associated with more than one production_type (so that, for example, production_id = 10 has type_id = 4 and type_id = 3) then yes, your structure is fine. If a production can only be associated with one production type, then you might as well just put the type_id in the production table itself and avoid the join.

As far as "Is this a correct way to structure a database for this particular purpose?", this is actually a vague and tricky question. Will this data model work? Yes. Is it the "correct way" to model it? No. Because there is no "correct way" in the first place. It all depends on what you need. If you had an enormous table filled with terabytes of data and low latency / high availability requirements, you'd want to denormalize this table as much as possible, and even consider using a NoSQL solution like Cassandra or Mongo or CouchDB (or any one of many others). Or if you were doing lots of inserts and had extremely low latency requirements, you might want to get rid of the foreign keys altogether. If you're comfortable with MySQL sharding, you might want to consider that.

There isn't really a "correct way" to model this. But yours is certainly a viable candidate. The next thing you need to consider is your application itself, and whether a normalized SQL database will allow your application to do what it needs to do. It probably will... but I'd still shy away from calling it a "correct" or "incorrect" way to model your data. It is a viable candidate though. That much is true.