--- In firebird-support@yahoogroups.com, "kogerbnz" <kogerbnz@...> wrote:

>
> Hello
>
> First, I hope this is the right group to ask a design question. If I
> choose the wrong one, then I'm sorry, but please tell me where to go.
>
>
> I have a database design problem, that I'm not sure I have solved in
> the best way.
>
> I have a user table. Each user can make a log once a day. So I'm
> creating a new table which has UserID as a foreign key and a primary
> key which is a combination of UserID (the FK) and a date column.
> Is that a good design?
>
> --UserTable--
> ID (PK)
> ...
>
> --DailyLogTable--
> UserID (FK and also Part of PK)
> Date (Part of PK)
> ...
>
>
> Or would it be better to have an artificial auto incremented PK, and
> make a constraint not allowing two rows with the same UserID and date.
>
> --DailyLogTable--
> ID (PK)
> UserID (FK)
> Date
> ...
>
> In a way the last seems a bit more straight forward, but does add an
> extra column and constraint.

Always use a surrogate field for your primary keys. (A field with no
real world meaning or significance). No matter how much people assure
you that field X is always unique or not null, things change. It is a
whole lot easier to change real world constraints if you don't have to
try and deal with foreign key dependencies as well.

So take your second approach and put a declared unique constraint
across (UserID, Date) if that is your business requirement. You can
use a before insert trigger to increment ID from a generator if it is
null eg

CREATE OR ALTER TRIGGER DAILYLOGTABLE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (NEW.ID IS NULL) THEN
BEGIN
NEW.ID = GEN_ID(DAILYLOGTABLEID, 1);
END
END
^

Adam