August 20, 2009 at 12:40 pm
Looking for some thoughts on schema here.
How would you best represent data where each row can have several related items, only one of which can be selected/current/valid at a time?
For example, in a database of Mughots of People, each Person could have one current Mugshot (but potentially several archived ones) for use in a photo ID. My initial thought feels like it has really tight circular references, and possibly other problems (eg, nothing to ensure a Person's selected Mugshot is actually a picture of the person):
[Table Mugshot]:
MugshotId
PersonId --FKID to Person represented in Mugshot
...MugshotDetails...
[Table Person]:
PersonId
SelectedMugshotId --FKID to selected Mugshot
...PersonDetails...
Is there a better way to represent this type of information?
August 20, 2009 at 1:00 pm
How about a person that has 0 mugshots. Is that allowed?
~BOT
Craig Outcalt
August 20, 2009 at 1:58 pm
Sure, I think it should be possible to have a Person without a selected Mugshot. Or, more generally, a list does not necessarily have to have a selected item.
A more generic way to describe my schema question: how to best model a database of Lists of Items, with each List optionally having a single selected Item? Should each Item need to reference the List it belongs to, or would a mapping table be more appropriate? (btw, an Item can only belong to one List).
My current solution seems very ugly. Basically, each Item row has an IsSelected column. For each set of Items with the same ListId, only one (or none) is allowed to have IsSelected set to true. This is maintained by the code in the application layer, and seems really hacky. Basically, I have:
[Table List]:
ListId
...ListDetails...
[Table ListItems]:
ItemId
ListId --FK to List this Item belongs to
IsSelected --bit, only one allowed per List
...ItemDetails...
This just doesn't feel quite "right", there must be a more proper way to handle this scenario. It seems like the currently selected Item should be a property of the List itself, not a "meta property" of the Item.
August 20, 2009 at 2:43 pm
The circular reference obviously won't allow you to insert data and the isSelected bit does create problems because the app is handling the data integrity (or you'd need to write a trigger to make sure not more than 1 isSelected was checked).
The other alternative is to have a 2 column table
person_SelectedMugshot
with a unique constraint on person (a person cant' have 2 selected mugshots) and FK's to the other two tables to ensure ref. integrity.
Craig Outcalt
August 20, 2009 at 4:10 pm
Thanks for the suggestion, I'll experiment with the 3rd table to see how it works out.
I still have one concern: there is nothing to prevent the SelectedItem from pointing to an Item that belongs to another List (or a Person's SelectedMugshot to point to a Mugshot belonging to another Person). To me, this is the sole benefit of the otherwise hacky IsSelected approach... if I perform a "select col1, col2 from Mugshots where PersonId=@personId and IsSelected=1" I am guaranteed to get either null, or a Mugshot of the right Person.
Is there a more normalized way of modeling this, while making sure a List's SelectedItem actually belongs to the proper List?
August 25, 2009 at 1:24 pm
Person's SelectedMugshot to point to a Mugshot belonging to another Person
same is true with a mugshot table with a personid. No gaurantee that the right person is in there... just that A person with that id exists.
~BOT
Craig Outcalt
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply