CELKO (9/10/2012)
We do not use IDENTITY for a key in RDBMS. Why do you think that the count of physical insertion attempts to one disk on one machine is an attribute of an entity in a valid data model? You ave a magical Kabbalah number called “id” that can be an issue, a squid, Lady Gaga, etc. That is magic and not RDBMS.
Most of experts here do use IDENTITY for a primary keys in SQL Server database where it's appropriate.
Mr. Celko doesn't, but it entirely his personal problem. It does look like he is afraid of Lady G and have some issues with Kabbalah magics :hehe:
Why do the data elements change names from table to table? Status is not an entity; it is an attribute property, such as “martial_status” “employment_status”, etc. An SQL programmer would have a column something lie this ...
It depends. Sometime it's good enough to have it as attribute. Sometimes you want to denormalize it into own entity.
I use the following approach when making a decision for status:
For simple cases like 2-4 different, pretty constant statuses (usually having more technical meaning than business, but still required some text description) I use simple table attribute with CHECK. Examples:
Record status (technical) : Open, Closed
Progress status: Not-started, Started, Completed, Failed
For cases which require more flexibility, eg. the probability of status description changed or new state added, I will have an entity:
Marital status: Single, Married, Divorced, Widowed (new statuses added recently: Partnership, Friendship, Life-enemies)
Job status: Employed, Not-employed, Self-Employed. - Here business may want to add some more statuses any time eg. Split Employed to Employed Full-time and Employed Part-time, add Temporary Employed, Never want to be Employed, Not-employable.:hehe: