Intersection tables or Common Super table

0
votes

I currently have a design like so. There are tables for each object class. Each table then contains rows (objects) which needs to be associated with multiple statuses.

Statuses table

  • id
  • status

Users

  • id
  • username
  • ...

Some other object class

  • id

  • ...

What's the best way to link each object class table to the status table? I am tossing up between the 2 methods described in SQL Antipatterns:

Intersection tables

users_has_statuses

  • user_id
  • status_id
  • UNIQUE(user_id, status_id)

etc. This will require an intersection table for each object class. So, as I add more object classes (tables), I will need to add an intersection table, which means that

Ancestor tables

object_ancestor

  • id

object_ancestor_statuses

  • object_ancestor_id
  • status_id
  • UNIQUE(object_ancestor_id, status_id)

Each object class would then have column with a foreign key linked to the id column in the object_ancestor table.

In terms of performance and design elegance, which way would be better?

1
mysqldatabase-designintersectionanti-patterns
I don't really get how "it is possible for objects to have the same status multiple times, which is bad thing." (in the last paragraph). – ypercubeᵀᴹ
If I have status: paid, active, inactive. I can have a user with statuses of paid and active. However, if one uses an ancestor table, I cant have UNIQUE(user_id, status_id). This means what if there was a bug with the application code, I can get multiple entries of "active" for a given user. – F21
What I understand as Ancestor Tables is that you can't have a user both paid and active. – ypercubeᵀᴹ
I have edited the post and added an extra table. This solves the problem with the database enforing each object to only have 1 copy of each status. However, which one provides better performance? Using intersection tables will lead to a lot of extra tables in the database. – F21
@phpdev: It leads to more tables, not to extra tables. (They're not extra; they're essential.) As a rough rule of thumb, given 'x' amount of data to be stored, expect more tables to improve performance. (As long as your tables are normalized to BCNF or 5NF.) – Mike Sherrill 'Cat Recall'

1 Answers

1
votes

My opinion is that the intersection tables way is better.

If you have many tables for entities with statuses and you use a single table for linking them to statuses, as you add more and more data, the performance will increasingly degrade (because when the DBMS will look for statuses of an entity, it will have to "go through" entries for all other types of entities).

Another problem with the ancestor way is that by looking at the database structure, you couldn't tell which types of entities have statuses. If you have people, dogs and rocks, each having entries in the ancestor table, you couldn't tell that rocks don't have statuses, for example.