April 30, 2024 at 9:55 am
We have a table with a numbers of rows in it. (Not that many rows, but it's the concept I'm interested in.)
One of these, and only one, can be selected by a user as the default row.
The obvious way to support this requirement is to add an IsDefault bit, not null, with a filtered unique index to ensure only one row has a value of 1. However, this doesn't seem like a good design. You're adding N values to a table in order to identify just one row. Changing the default in a way that doesn't temporarily violate the unique index is slightly annoying. (You'd need to update both rows in the same update statement, one back to 0 from 1 and the other to 1 from 0.)
The best alternative I can think of is a separate table instead of an IsDefault column. This other table has one row with one column, FK to the PK of the data table, not null. This will hold the PK value of the default row. This solves a number of requirements by design:
I was just wondering how this requirement (one default row) is generally met and what people think of this suggestion.
Thanks in advance.
April 30, 2024 at 10:27 am
This was removed by the editor as SPAM
April 30, 2024 at 8:58 pm
The best alternative I can think of is a separate table instead of an IsDefault column. This other table has one row with one column, FK to the PK of the data table, not null. This will hold the PK value of the default row.
that was my first thought and what I would do. You could expand it further and hold user detail to identify a default row for different users
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 30, 2024 at 9:55 pm
This was removed by the editor as SPAM
May 1, 2024 at 7:47 am
Thanks for your replies. I should have made it clearer in my original post - we need to be able to select one row as the default, not one row per user. It's just that a user can make the selection. But I don't think that materially changes anything - we can store the PK of that one row in our new table. And we can obviously manage more than one "table needing a default row" in the new table - one column per such table.
Does anybody know of any drawbacks with this approach?
May 1, 2024 at 8:23 am
And we can obviously manage more than one "table needing a default row" in the new table - one column per such table.
Does anybody know of any drawbacks with this approach?
One column per table violates 3NF. Wouldn't a better structure be something like (TableName, DefaultPK)?
(With schema name added if needed.)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 1, 2024 at 8:26 am
Does anybody know of any drawbacks with this approach?
can’t think of any immediately, the table is small and lightweight.
only the obvious which is the inability to truncate the parent table or delete rows as the FK ref integrity will maintain relationships to the child table
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 1, 2024 at 12:04 pm
julian.fletcher wrote:And we can obviously manage more than one "table needing a default row" in the new table - one column per such table.
Does anybody know of any drawbacks with this approach?
One column per table violates 3NF. Wouldn't a better structure be something like (TableName, DefaultPK)?
(With schema name added if needed.)
Argh - yes. But how could you ensure that the value for DefaultPK in a given row existed in the table referenced by TableName? My 3NF-violating approach did allow for a FK to do this!
May 1, 2024 at 12:08 pm
Argh - yes. But how could you ensure that the value for DefaultPK in a given row existed in the table referenced by TableName? My 3NF-violating approach did allow for a FK to do this!
An excellent consideration, and one which may well justify the 3NF violation, because there is no elegant way of doing this that I can think of.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 1, 2024 at 12:16 pm
I don’t see any reason to be concerned about 3NF reqs here it’s a simple metadata table
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 1, 2024 at 12:41 pm
I don’t see any reason to be concerned about 3NF reqs here it’s a simple metadata table
Unless the number of tables containing defaults is likely to keep changing, I agree.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 4, 2024 at 6:15 pm
This was removed by the editor as SPAM
May 4, 2024 at 6:16 pm
This was removed by the editor as SPAM
May 7, 2024 at 12:36 am
Create table list_of_stuff
(stuff_id char (10) not null primary key,
...));
Create view anointed_one
as
SELECT ...
From list_of_stuff
where stuff_id = <parameter>
Since you deal with the table, we know we've got a key, so why not just put your anointed value in a single-row view? This avoids using bit flags so people won't think you're an assembly language programmer and gives you flexibility about how you pick the anointed value(s).
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply