Re: Table design question

Karel Miklav <karel_at_inetis.spppambait.com> wrote in message news:<ED4Qb.1906$%x4.322575_at_news.siol.net>...
> Hi,
>
> constantly facing this design issue I decided to ask for some help.
> Consider the tables below; there is a user with historical data and a
> related table log:
>
>
> CREATE TABLE user (
> id INT NOT NULL ) <---------------------
> |
> CREATE TABLE user_history ( |
> user_id INT NOT NULL, oo----------------|
> valid_from DATETIME NOT NULL, |
> name CHAR(100) NOT NULL, |
> ... ) |
> |
> CREATE TABLE log ( |
> id INT NOT NULL, |
> tstamp DATETIME NOT NULL, |
> user_id INT NOT NULL, oo----------------
> description CHAR(100) NOT NULL,
> ... )
>
> There are other related tables like log here, where I'm really not
> interested in historical data but just the identity of the user, and
> those where exact user properties in point of time matter.
>
> The one-field user table smells like a design problem. Merging user and
> user_history tables introduces ugly problems with relational integrity.
> Are these one-field tables normal or how am I supposed to tackle this?
> Please share your thoughts and links.
>
> Thanks,
> Karel Miklav

There is nothing wrong with having a one-column, key-only table - though I have known people who considered this to be illegal for some reason. On one occasion I actually had to add an additional non-key column (e.g. user_name for your example) just to appease them!

The bottom line is: a user is an entity, and so there should be a table in which a user is defined once only; neither user_history nor log fulfill this function. Received on Fri Jan 23 2004 - 15:39:42 CET