CODEWITHSUNDEEP
sqlconstraints
eliocs
eliocs

Reputation: 18827

Complicated logic constraints

I'm trying to enforce data correctness on an schema of the following characteristics:

Table:

|SAVE_TYPE|PROFILE|USERNAME|DATA|

Depending on the save type the profile column or the username column will be used. Imagine if a want to save the data at a profile scope then I'll insert the following row:

|SAVE_TYPE|PROFILE|USERNAME|DATA|
|PROFILE  |PROF. 1|Mr. X   |123 |

This row will mean that the data will be available for the profile PROF. 1 and was inserted by the user Mr. X. On the other hand in this case:

|SAVE_TYPE|PROFILE|USERNAME|DATA|
|USER     |NULL   |Mr. X   |456 |

This row means that the data will be available only for Mr. X.

I don't know what would be the best way to implement the constraint for this behaviour, may be I should be using another table structure (I'm open to change the schema). For now what I have is a unique constraint of the SAVE_TYPE, PROFILE and USERNAME columns, but this is now completely correct. Wrong data that my model accepts:

Multiple instances on SAVE_TYPE -> USER for the same user.

|SAVE_TYPE|PROFILE|USERNAME|DATA|
|USER     |PROF. 1|Mr. X   |456 |
|USER     |PROF. 2|Mr. X   |456 |
|USER     |PROF. 3|Mr. X   |456 |

Multiple instances on SAVE_TYPE -> PROFILE for the same profile.

|SAVE_TYPE|PROFILE|USERNAME|DATA|
|PROFILE  |PROF. 1|Mr. X   |123 |
|PROFILE  |PROF. 1|Mr. Y   |123 |
|PROFILE  |PROF. 1|Mr. Z   |123 |

Thanks in advance, hope I've made things clear :)

Upvotes: 0

Views: 65

Answers (1)

Erwin Smout
Erwin Smout

Reputation: 18408

"This row will mean that the data will be available for the profile PROF. 1 and was inserted by the user Mr. X."

and

"This row means that the data will be available only for Mr. X."

If your "meanings for rows" are SO different, then this is an indication that what you have at hand should really be two distinct tables.

In terms of relational theory : each relvar ("table") has ONE associated external predicate ("what the rows in the table actually mean"). Therefore if you have two distinct predicates, you should also have two distinct relvars.

Upvotes: 2

Related Questions