-1

I have free and paid themes tables. Free themes have download link Paid themes have buy link and price.

Here is a database schema in graphical format:

"theme" can be either "paid theme" or "free theme" with different fields

A theme cannot be both paid and free. How can I prevent both tables (FreeThemes, PaidThemes) from having data for a theme at the same time?

tripleee
  • 175,061
  • 34
  • 275
  • 318
Fırat Kaya
  • 87
  • 1
  • 5
  • That looks like normalisation gone to far are you stuck with this design? If so validate with insert and delete triggers on freethemes and paidthemes – P.Salmon Jan 05 '22 at 13:58
  • I'm trying to follow the normalization rules, but I think I'm exaggerating a bit. Do you think this design is good or does it need to be redesigned? – Fırat Kaya Jan 05 '22 at 14:00
  • Put it this way if you have to go through hoops then it's probably too far and I don't know what you are saving by splitting into 3 tables. I would consider having 1 table and a column to indicate it theme is free or paid. That way you lose join complications at the small expense of holding a flag and a price.- Of course if your schema is simplified for the question this route may not be appropriate. – P.Salmon Jan 05 '22 at 14:03
  • 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 Jan 10 '22 at 02:37

4 Answers4

2

This is an example of Class table inheritance. It's a perfectly valid design choice (I do not agree it's " normalisation gone too far") - it's a way of working around one of the limitations of the relational model.

Another limitation is that there is no clean, native way of declaratively asserting the business rule you need.

There are a few common ways to work around that.

The first is to delegate the responsibility to the application layer. If there's a single application/service connecting to the database, this isn't terrible - especially if you can wrap the logic in unit tests etc.

The second is to embed the logic in triggers. This allows you to guarantee this business rule even if many applications use the database, but you'll have to change the trigger when your business logic changes (e.g. if you need to add a new sub class).

The final option is to add a "type indicator" to the Themes table, and to use that flag, rather than the presence of a row in the subclass tables, to determine whether a theme is free or paid. Again, not super elegant, and requires the client application to honour that flag, but it does express the business intent neatly.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
1

A word about subtypes. The proper way to implement constraints for subtypes would be to use assertions (CREATE ASSERTION), but it is still not available in major DBs. I am using FKs instead, and as all other substitute methods it is not perfect. People argue a lot, on SO and SE-DBA, what is better. I would encourage you to check other methods too.

-- Theme THM, of theme-type THM_TYP exists.
--
theme {THM, THM_TYP, -- other_common_attributes}
   PK {THM}
   SK {THM, THM_TYP}

CHECK THM_TYP in ('F', 'P')

-- Free theme THM (of theme-type 'F') exists.
--
free_theme {THM, THM_TYP,
           -- other attributes specific to free}
        
PK {THM}

FK {THM, THM_TYP} REFERENCES theme {THM, THM_TYP}

CHECK (THM_TYP = 'F')

-- Paid theme THM (of theme-type 'P') exists.
--
paid_theme {THM, THM_TYP,
           -- other attributes specific to paid}

PK {THM}

FK {THM, THM_TYP} REFERENCES theme {THM, THM_TYP}

CHECK (THM_TYP = 'P')

Note:

All attributes (columns) NOT NULL


PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key

Following formal normalization rules will not get you to this solution. Both, free_theme and paid_theme tables have the FD {} --> {THM_TYP}; in other words attribute THM_TYP does not depend on the PK {THM}, hence these tables are not in 2NF.

In this case FK and CHECK constraints prevent anomalies and logical errors -- which is the objective of the normalization in the first place.

If you have a problem with tables not being in 2NF, here is a way to think about this:

  1. Make sure that free_theme and paid_theme tables are in high NF (5NF) without the THM_TYP attribute.
  2. Add THM_TYP in order to deal with the problem, and understand the compromise.
  3. Remember that the root cause of the problem is the lack of the required cross table constraint (assertion) in current SQL implementations.
Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71
0

KISS. One table for both -- Let the price be 0.00 to indicate free.

(Or NULL could indicate "free", but I don't see any advantage.)

Rick James
  • 135,179
  • 13
  • 127
  • 222
0

NEW FIELD IN THEMES TYPE YES OR NOT (CHEKBOX)

If yes then "free"; other case "paid"

THE BEST
  • 1
  • 1