September 24, 2003 at 8:14 am
Hello!
I have to create a table that represents a object, named "Node". This object can have many states, like "visible", "locked", "invalid", etc. I would like to know if there is a "Best Practice" to create this kind of table...
As far as I can see, there are two ways to handle this: create a column for each state or just one column and use a byte (00110...) to represent if the states are on or off. To me both solutions have downsides:
1)creating a column for each state:
not too much problem if few states, but what if someday I come up with 50 states?? The table is going to get pretty ugly..
2)using a bit mask:
isn't this solution very error-prone, since I have to relly heavily on documentation to know with bit represents each state?
Someone could suggest to separete these states in groups and create a table for each group... is this the best solution? Won't it just move the overhead of querying a lot of columns with querying a lot of tables?
Does anyone have a opinion about this?
Thank you in advance!
September 24, 2003 at 9:13 am
Isn't this just a classic case of using first normal form with a many-to many relation?
Create a States table with a primary key (tinyint IDENTITY?) and description for each state. Then create an associative table with a primary key compounded from the (foreign keys to this table) primary keys of the Nodes and States tables. Insert a row for each operative combination of Node and State.
--Jonathan
--Jonathan
September 24, 2003 at 10:46 am
Geez, I must have skipped a few classes...
Thanks for not flamming me! 🙂
September 25, 2003 at 6:38 am
Jonathan is right on the mark.
Further, if you get into a situation where your objects may need to have a group of states, create a states_group table, creating state groups. The state groups contain the individual states from Jonathan's state table. Then assign the Node object with a state_group, rather than a single state.
An example may be where an object may need different states for Web UI, Win32 UI, security, etc.
AFP
What's the business problem you're trying to solve?
What's the business problem you're trying to solve?
September 29, 2003 at 9:01 am
FYI. A bit column takes at least 1 byte. However, SQL Server will store up to 8 bit columns in the same byte. So, 52 state bit flags would take only 7 bytes. So, while ugly, your table might not take a lot memory.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply