CODEWITHSUNDEEP
phpmysqlsqldatabase
aWebDeveloper
aWebDeveloper

Reputation: 38392

Single Category Table For Entire Site Or Different For Different Use Case

product table has category, media table has category, ticket table has category.

Each of these has a HasMany relation with category table. There are two ways of doing it:

  1. Have a common Category table with probably a type column and have intermediatory table like MediaCategory, etc.
  2. Have separate tables like MediaCategory with each having same structure as category

Upvotes: 0

Views: 49

Answers (2)

Eli Algranti
Eli Algranti

Reputation: 9007

IF the categories are not shared then it is best (in most cases) to have separate tables for each category type.

Here's the rationale:

The database is the gatekeeper of your data's relational integrity. In a well written program there should not be any foreign key violation exceptions, i.e. the code does not rely on the database to keep the relational integrity of the data. However should a bug creep-in, the relations in the database make the bug less likely to cause data corruption.

When using separate tables for media, products, etc. and their valid categories, the relational integrity can be easily maintained with a foreign key relationship; essentially any record in the media table can belong to any category in the media categories table. Ensuring the relation:

"Records in media table can belong to any category of type 'media' in 
the categories table"

is less straight forward at the database level.

That being said, a problem whose solution is duplication of data structures makes the whole underlying structure suspect. This may not be so in your case, but you should look at the underlying use-cases that require the categories to be introduced and see whether they are better served in a different manner (say Free Text Search indexed keywords.)

Upvotes: 0

Lolitha Ratnayake
Lolitha Ratnayake

Reputation: 308

First one is better I think in point of integrity.

Upvotes: 1

Related Questions