-2

I have two sets of data that are near identical, one set for books, the other for movies.

So we have things such as:

  • Title
  • Price
  • Image
  • Release Date
  • Published

etc.

The only difference between the two sets of data is that Books have an ISBN field and Movies has a Budget field.

My question is, even though the data is similar should both be combined into one table or should they be two separate tables?

I've looked on SO at similar questions but am asking because most of the time my application will need to get a single list of both books and movies. It would be rare to get either books or movies. So I would need to lookup two tables for most queries if the data is split into two tables.

panthro
  • 22,779
  • 66
  • 183
  • 324
  • how do define books/movies in Title Price Image Release Date Published – Rahul SK May 10 '20 at 15:37
  • 2
    It's up to you. Personally, I think books and movies are sufficiently different that they can have their own tables, but it's really down to your business case, and the kinds of queries your likely to be executing against this data – Strawberry May 10 '20 at 15:47
  • ISBN? I guess IBAN means International Bank Account Number. – O. Jones May 10 '20 at 16:22
  • What will your SELECTs look like? That may give you the answer about the schema. – Rick James May 10 '20 at 20:43
  • Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy May 11 '20 at 02:02

3 Answers3

1

You must be very sure about future requests/features for your application.

I can't image what type of books linked with movies you store thus a lot of movies have different titles than books which are based on. Example: 25 films that changed the name.

If you are sure that your data will be persistent and always the same for books and movies then you can create new table for example Productions and there store attributes Title, Price, Image, Release Date, Published. Then you can store foreign keys of Production entity in your tables Books and Movies.

But if any accident happen in the future you will need to rebuild structure or change your assumptions. But anyway it will be easier with entity Production. Then you just create new row with modified values and assign to selected Book or Movie.

Solution with one table for both books and movies is the worst, because if one of the parameters drive away you will add new row and you will have data for first set (real book and non-existing movie) and second set (non-existing book and real movie).

Of course everything is under condition they may be changes in the future. If you are 100% sure, then 1 table is enough solution, but not correct from the database normalization perspective.

I would personally create separate tables for books and movies.

Jsowa
  • 9,104
  • 5
  • 56
  • 60
1

The only difference between the two sets of data is that Books have an IBAN field and Movies has a Budget field.

  • Are you sure that this difference that you have now will not be extended to other differences that you may have to take into account in the future?

  • Are you sure that you will not have to deal with any other type of entities (other than books and movies) in the future which will complicate things?

If the answer in both questions is "Yes" then you could use 1 table.
But if I had to design this, I would keep a separate table for each entity.
If needed, it's easy to combine their data in a View.
What is not easy, is to add or modify columns in a table, even naming them, just to match requirements of 2 or more entities.

forpas
  • 160,666
  • 10
  • 38
  • 76
1

Doing this -- cataloging books and movies -- perfectly is the work of several lifetimes. Don't strive for perfection, because you'll likely never get there. Take a look at Worldcat.org for excellent cataloging examples. Just two:

My suggestion: Add a table called metadata. your titles table should have a one-to-many relationship with your metadata table.

Then, for example, titles might contain

title_id     title                                  price      release  
103          Designing Data-Intensive Applications  34.96      2017
104          Coco                                   34.12      2107

Then metadata might contain

 metadata_id   title_id    key             value
  1            103         ISBN-13         978-1449373320
  2            103         ISBN-10         1449373320 
  3            104         budget          USD175000000
  4            104         EIDR            10.5240/EB14-C407-C74B-C870-B5B6-C
  5            104         Sound Designer  Barney Jones

Then, if you want to get items with their ISBN-13 values (I'm not familiar with IBAN, but I guess that's the same sort of thing) you do this

  SELECT titles.*, isbn13.value isbn13
    FROM titles
    LEFT JOIN metadata isbn13 ON titles.title_id = metadata.title_id
                             AND metadata.key='ISBN-13'

This is a good way to go because it's future-proof. If somebody turns up tomorrow and wants, let's say, the name of the most important character in the book or movie, you can add it easily.

O. Jones
  • 103,626
  • 17
  • 118
  • 172