0

i have a simple database with 3 tables.

In the users table i simply store the information and his role. In the locations table i simply store more user information.

Now here is the thing, the column pickup_instrucntions is a optional column, meaning only a user with a specific role can store. So an example would be a user with role_id = 1 can't store in pickup_instrucntions but user with role_id = 2 can, leaving one row NULL and the other with inserted data.

Here is my schema: enter image description here

Now it does get the job done but is this is good practice? Is there a better way around this?

Shadow
  • 33,525
  • 10
  • 51
  • 64
Raitiko
  • 165
  • 11

1 Answers1

1

Honestly, most application I've seen just allow the optional column to have NULL, and leave it to the application code to "do the right thing."

You could design a schema that prevents improper use of that attribute, but that gets into Fifth Normal Form territory. You can do it, but it takes time and focus to design a schema like that. It's more work than most people want to put into a single attribute in one table.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Nullable columns & rearrangements for subtypes doing or undoing outer joins have nothing to do with normalization to higher NFs & its reconstructions via natural joins per se. Some SQL idioms for constraining subtypes involve a column with only one (type tag) value, which violates 2NF, but it doesn't matter since such a table is essentially a view & the denormalization problems are traded off. – philipxy Mar 17 '23 at 02:19
  • @philipxy It sounds like you want to post an answer. – Bill Karwin Mar 17 '23 at 03:11