status | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

status

CREATE TABLE [dbo].[STATUS] (
[StatusID] [int] NOT NULL ,
[LongName] [nvarchar] (100),
[ShortName] [nvarchar] (50),
[EntityName] [nvarchar] (30),
Code:
 [nvarchar] (10),
[SortBy] [int],
[Retired] [bit]
is above a good design or should it be broken up into below?
Individual_Status
Organization_Status
etc...
reasons?
 

I prefer to have separate lookup table for statuses that are applied to different entities. Also, if you don’t expect hundreds of thousands rows in the table change type to smallint or even tinyint. This way you will have more efficient indexes containing this column. It will be part of clustered index (in related tables) at least. <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />
why do you prefer this? "separate lookup table for statuses that are applied to different entities"
The name of the table is a bit confusing: in a table called STATUS we would expect to find a relatively short list of different values for STATUS, not all your individuals and organisations with a checkbox for ‘Retired’. The status is not the key information for this table, so the table should really be called something like CLIENT (the client being either an individual or an organisation). The CODE column appears to be the natural key to this table, with the LONGNAME column presenting the full name. So instead of StatusID you should use the natural key, and you might want to change the name CODE to CLIENT. It all depends on how far your system is already worked out. But my suggestion would be to read up on relational database design basics.
sample record:<br /><br />|1| |Active| |Act| |Organization| |A| |NULL| |0|<br /><br /><br />back to my question.<br />(<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /><br />why…<br />"separate lookup table for statuses that are applied to different entities"<br /><br /><br />
The question is: One row (i.e. organization or Individual) may have no sense values?.
I mean: If one row about Organizations have one o more values with no sense but have sense with Individual and viceverza, then you have separate one table for each.
I suppose that’s what mmarovic think.
Why?, because normalization rules. (Sorry I can’t write the rules in good English).
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">I mean: If one row about Organizations have one o more values with no sense but have sense with Individual and viceverza, then you have separate one table for each.<br />I suppose that’s what mmarovic think.<br />Why?, because normalization rules. (Sorry I can’t write the rules in good English).<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">Exactly. Luis, thank you for interpretation <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br />What is a point of having one general lookup table? I don’t see any advantage of it.<br />You can’t put foreign key on Individual and Organization tables and your queries accessing Status table will be more complex.<br /><br />Better create Individual_Status and Organization_Status tables as you mentioned in original post.<br />
]]>