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:

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?

CodePudding user response：

You need an extra step, an extra table, for example ThemeCost table. You add the variable, let's say 'cost' = Yes or No /Paid or Free, such as e.g. char 1 for Yes/Paid and char 2 for No/Free.
Then you have the FreeThemes and the PaidThemes tables. Free has the downloads link and the Paid has the price variable, etc.

EDIT:
Thinking about your first table, I wouldn't know how far you might expand later on, you may also add the 'Cost' variable into your first table and from there depending on the variable value, can be send to either of the two tables. I usually keep some of the stuff in the first table separate, so later on when you have huge data and you need queries to gather fast, let's say the description, I would keep separate from the first table.

CodePudding user response：

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.

CodePudding user response：

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.