Database design question

Hi all,

I have a design question:

I have a bunch of users (name, address, zip, etc.). They are assigned a
card with a specific id.
The only thing unique is this card id, or probably the combination of
all other user fields.
So it's seductive to use the card id as the primary key.
This card allows access to certain places and all access is logged.

The problem is, a user might lose a card and will be issued a new one
with a different id. Perhaps it may even be desirable to assign
mutliple cards with different ids to one person, but let's put that
scenario aside for a moment.

Issuing a new card to a user could mean updating the card id with a new
one, e.g.:
UPDATE Users SET CardId=124 WHERE CardId=123

But same would have to be done with all 'logs' that refer to the lost
card id.
And some kind of logging of this change needs to occur to maintain some
'history'.
Consistency could be handled with triggers or something..
Doesn't seem like a good solution.

It seems obvious that I should seperate user from card id, where card
id is no longer primary key, rather just a field within the users
table, or if multiple cards can be assigned to the same user, a
seperate table for cards mapped to user records.

So then how do I uniquely identify a user? It seems easy enough to
simply use an IDENTITY column for this, however, I do agree with
'CELKO's' (sp?) principle that best design is one where data in the
database actually represents something in the real world. In particular
when backing up, reinserting and other things could goof up
(re-generate) these id's.

And there's my dilemma. How do I uniquely identify each user record
consisting of just name, address, zip, city and perhaps phone number?
There are no other id's that represent the user, such as social
security number or whatever.

Actually, the card is going to be assigned to a household (home
address), so the combination of house number, street and zip is
probably unique but using this as a primary key would make all queries
quite cumbersome, compared to some id.

Is this a case where an identity (aka auto_increment) is inevitable or
even highly recommended?

Another alternative I thought of was to use the card id as primary key,
but have the same user record appear in the users table multiple times,
but with a different card id, where each card id might actally be
'blocked' or not. This blocked cards can then be deleted once the
records in other tables that refer to that id are also deleted, such as
logs. Sounds ugly as this introduces some redundancy of user records,
but might actually be acceptable I think, because logs are deleted
every now and then and lost cards will be rare. Not sure if I should
design the database with the 'rule' or 'exception' in mind.

Yes, I'm a rookie.. and I don't mind being chewed out for it, as long
as the lecture includes something useful I can work with and learn from
:)

Lisa

re: Database design question

reageer@yahoo.com wrote:[color=blue]
> Hi all,
>
> I have a design question:
>
> I have a bunch of users (name, address, zip, etc.). They are assigned a
> card with a specific id.
> The only thing unique is this card id, or probably the combination of
> all other user fields.
> So it's seductive to use the card id as the primary key.
> This card allows access to certain places and all access is logged.[/color]

If more than one user can live in a household you can't use the
card id as the primary key for the user since you state that the
card is issued to a household and not to a user.
[color=blue]
>
> The problem is, a user might lose a card and will be issued a new one
> with a different id. Perhaps it may even be desirable to assign
> mutliple cards with different ids to one person, but let's put that
> scenario aside for a moment.
>[/color]
A second reason why card id cannot be used as the primary key.
[color=blue]
> Issuing a new card to a user could mean updating the card id with a new
> one, e.g.:
> UPDATE Users SET CardId=124 WHERE CardId=123
>
> But same would have to be done with all 'logs' that refer to the lost
> card id.
> And some kind of logging of this change needs to occur to maintain some
> 'history'.
> Consistency could be handled with triggers or something..
> Doesn't seem like a good solution.[/color]

You can't avoid the problem of a change in primary key values
when dealing with people because names are not permanent. You
have a situation in which there aren't any truly good solutions,
but if you keep your logs in the database and use triggers or
separate procedures to update the logs as well as the active
tables maintenance shouldn't be a significant problem.
[color=blue]
>
> It seems obvious that I should seperate user from card id, where card
> id is no longer primary key, rather just a field within the users
> table, or if multiple cards can be assigned to the same user, a
> seperate table for cards mapped to user records.
>
> So then how do I uniquely identify a user? It seems easy enough to
> simply use an IDENTITY column for this, however, I do agree with
> 'CELKO's' (sp?) principle that best design is one where data in the
> database actually represents something in the real world. In particular
> when backing up, reinserting and other things could goof up
> (re-generate) these id's.[/color]

Why not use area code, phone number, first name, Suffix (Jr.,
Sr., etc). Phone numbers and first names tend to be stable and
the suffix is sufficient to discriminate between members of a
household with the same first name.[color=blue]
>
> And there's my dilemma. How do I uniquely identify each user record
> consisting of just name, address, zip, city and perhaps phone number?
> There are no other id's that represent the user, such as social
> security number or whatever.
>
> Actually, the card is going to be assigned to a household (home
> address), so the combination of house number, street and zip is
> probably unique but using this as a primary key would make all queries
> quite cumbersome, compared to some id.
>[/color]
Beware of using addresses, or any multiple word value, as a key
since you can't predict how the users will enter data. For
example "1st Street" or "First Street" or "1st ST" or "First ST"
[color=blue]
> Is this a case where an identity (aka auto_increment) is inevitable or
> even highly recommended?[/color]

auto_increment should only be used as a last resort because
there is no way to relate the auto_increment value to the data
to assure that the correct data is being operated on. When you
use values like phone numbers and names you can sight verify
that you are operating on the correct data.[color=blue]
>
> Another alternative I thought of was to use the card id as primary key,
> but have the same user record appear in the users table multiple times,
> but with a different card id, where each card id might actally be
> 'blocked' or not. This blocked cards can then be deleted once the
> records in other tables that refer to that id are also deleted, such as
> logs. Sounds ugly as this introduces some redundancy of user records,
> but might actually be acceptable I think, because logs are deleted
> every now and then and lost cards will be rare. Not sure if I should
> design the database with the 'rule' or 'exception' in mind.
>[/color]
You are right. This is ugly. Also by introducing the concept
of redundant data you are subverting one of the primary
strengths of a relational data base management system. Don't do it.

[color=blue]
> Yes, I'm a rookie.. and I don't mind being chewed out for it, as long
> as the lecture includes something useful I can work with and learn from
> :)
>
> Lisa
>[/color]

re: Database design question

I would generate a unique userid for each user. Not being a big fan of
auto_increment I would create a table like:

CREATE TABLE UserIDS(
LastUserID int)

When you create a new User, get the current userid from this table and then
add 1 to LastUserID. This gives you complete control. Downside is this table
might become locked if many users are being created at the same time when
the DBMS uses table locking. I'm not sure what MYSQL does.

I know Celko has a problems with IDENTITY and AUTO_INCREMENT, but I didn't
think he had a problem with a synthetic key. As a matter of fact, one could
argue that you don't want semantics in your key.

Also what's interesting is that a user might have several cards.

Rich
<reageer@yahoo.com> wrote in message
news:1133345215.453099.230850@g49g2000cwa.googlegr oups.com...[color=blue]
> Hi all,
>
> I have a design question:
>
> I have a bunch of users (name, address, zip, etc.). They are assigned a
> card with a specific id.
> The only thing unique is this card id, or probably the combination of
> all other user fields.
> So it's seductive to use the card id as the primary key.
> This card allows access to certain places and all access is logged.
>
> The problem is, a user might lose a card and will be issued a new one
> with a different id. Perhaps it may even be desirable to assign
> mutliple cards with different ids to one person, but let's put that
> scenario aside for a moment.
>
> Issuing a new card to a user could mean updating the card id with a new
> one, e.g.:
> UPDATE Users SET CardId=124 WHERE CardId=123
>
> But same would have to be done with all 'logs' that refer to the lost
> card id.
> And some kind of logging of this change needs to occur to maintain some
> 'history'.
> Consistency could be handled with triggers or something..
> Doesn't seem like a good solution.
>
> It seems obvious that I should seperate user from card id, where card
> id is no longer primary key, rather just a field within the users
> table, or if multiple cards can be assigned to the same user, a
> seperate table for cards mapped to user records.
>
> So then how do I uniquely identify a user? It seems easy enough to
> simply use an IDENTITY column for this, however, I do agree with
> 'CELKO's' (sp?) principle that best design is one where data in the
> database actually represents something in the real world. In particular
> when backing up, reinserting and other things could goof up
> (re-generate) these id's.
>
> And there's my dilemma. How do I uniquely identify each user record
> consisting of just name, address, zip, city and perhaps phone number?
> There are no other id's that represent the user, such as social
> security number or whatever.
>
> Actually, the card is going to be assigned to a household (home
> address), so the combination of house number, street and zip is
> probably unique but using this as a primary key would make all queries
> quite cumbersome, compared to some id.
>
> Is this a case where an identity (aka auto_increment) is inevitable or
> even highly recommended?
>
> Another alternative I thought of was to use the card id as primary key,
> but have the same user record appear in the users table multiple times,
> but with a different card id, where each card id might actally be
> 'blocked' or not. This blocked cards can then be deleted once the
> records in other tables that refer to that id are also deleted, such as
> logs. Sounds ugly as this introduces some redundancy of user records,
> but might actually be acceptable I think, because logs are deleted
> every now and then and lost cards will be rare. Not sure if I should
> design the database with the 'rule' or 'exception' in mind.
>
> Yes, I'm a rookie.. and I don't mind being chewed out for it, as long
> as the lecture includes something useful I can work with and learn from
> :)
>
> Lisa
>[/color]

re: Database design question

>I have a design question:[color=blue]
>
>I have a bunch of users (name, address, zip, etc.). They are assigned a
>card with a specific id.
>The only thing unique is this card id, or probably the combination of
>all other user fields.
>So it's seductive to use the card id as the primary key.
>This card allows access to certain places and all access is logged.[/color]

I think you need the concept of what many businesses call an "account
number". This need not be given to the customer (and if you keep
the account number secret from the customer there will be fewer
requests to change the account number because it is "offensive",
often because it includes the Number of the Beast), and it could
be an autoincrement field. It is a good candidate for a primary
key. It should *NOT* contain any information by itself that might
require that it be changed (e.g. no coding the branch number into
it).

If you *do* give the account number to the customer, it is a good
idea to assign a number not easily guessed or typo'd from one valid
account number to another. The Luhn checksum used on credit card
numbers is trivially hacked but it catches 90% of pure random guesses
and 100% of single-digit errors, likely to occur when reading the
number over the phone.

You need to think carefully about the situations of a person having
more than one account, and more than one card on the same account,
and how these are supposed to work.
[color=blue]
>The problem is, a user might lose a card and will be issued a new one
>with a different id. Perhaps it may even be desirable to assign
>mutliple cards with different ids to one person, but let's put that
>scenario aside for a moment.[/color]

Can card numbers *EVER* be reassigned? I'm assuming, from the idea
that a card might be "lost", that it is a physical object with
preassigned numbers you have little control over, and that it costs
money to replace, so there is some incentive to re-use cards. (This
is the "parking garage access card" model.) I'll assume there might
be recovery of lost cards that get returned to you, or that people
closing their accounts turn in the cards.

You need a table to translate card numbers to account numbers. If
card numbers are never re-assigned, a card is associated with no
more than one account number, and it is either active or inactive.
The primary key is the card number.

If card numbers are re-assigned, the table contains a card number,
an account number, and a start and end date for which the association
is valid. Active cards have entries with the end date null or in
the future. Assuming that you don't go nuts re-using cards within
24 hours, (card number, start date) is good as a primary key.
Otherwise the start and end dates need to include times also.
It is important that the start/end time ranges NOT overlap.
This you could enforce with triggers.

Either way, you NEVER delete records from the cards to account table.
Inactive records are needed to interpret past logs.
[color=blue]
>Issuing a new card to a user could mean updating the card id with a new
>one, e.g.:
>UPDATE Users SET CardId=124 WHERE CardId=123[/color]

In the 'reuse cards' situation, the lost card or turned-in card
would be invalidated by filling in the end date (You need to decide
what the end date means: does the card die at 12:01AM or 11:59PM
on the end date?). The new card would have a new entry added for
the new card, account, start date of today, and end date of NULL.
[color=blue]
>But same would have to be done with all 'logs' that refer to the lost
>card id.
>And some kind of logging of this change needs to occur to maintain some
>'history'.[/color]

The cards to account table is your log of the changes of card
ownership. Past logs need not be changed.
[color=blue]
>Consistency could be handled with triggers or something..
>Doesn't seem like a good solution.[/color]

Logs containing card numbers could be matched to accounts
with the card number and a time stamp.

SELECT account FROM cards_to_account
WHERE cardno = '$card_number' and '$time_stamp' >= valid_start_date
and (valid_end_date is null OR '$time_stamp' <= valid_end_date)

You do not need to alter past logs. If a past log shows a granted
access which does not return an account number with the above query,
something strange is going on. You still have a complete list of
known users of that card, with time ranges. If card numbers are never
re-issued, the parts relating to time stamps can be omitted, and
you just look up by card number.
[color=blue]
>It seems obvious that I should seperate user from card id, where card
>id is no longer primary key, rather just a field within the users
>table, or if multiple cards can be assigned to the same user, a
>seperate table for cards mapped to user records.
>
>So then how do I uniquely identify a user? It seems easy enough to
>simply use an IDENTITY column for this, however, I do agree with
>'CELKO's' (sp?) principle that best design is one where data in the
>database actually represents something in the real world. In particular
>when backing up, reinserting and other things could goof up
>(re-generate) these id's.[/color]

There are few things in the real world that cannot change. (names,
addresses, phone numbers, SSNs, etc.) It's not a coincidence that
businesses of all sizes use things like account numbers that have
no real-world meaning to tie together records with fields that do.

Also, you need to be able to deal with corporate accounts where the
corporation has dozens or thousands of individual accounts which
are given to the holder of a specific job title (although the
person may change frequently, perhaps without telling you).
Consider also the need to have many accounts on a common bill.
Even if you don't bill, you may have one company contact with
administrative control to cancel accounts of ex-employees and
create new ones.

When you attempt to insert a row with a specific value in it, MySQL
will take it as it is (and that includes restoring backups made
with mysqldump). If your triggers interfere with that, you should
re-write them to avoid this.
[color=blue]
>And there's my dilemma. How do I uniquely identify each user record
>consisting of just name, address, zip, city and perhaps phone number?
>There are no other id's that represent the user, such as social
>security number or whatever.[/color]

That's why businesses use account numbers. This does *NOT* mean
that you have to ask the user to read you his account number. In the above
situation, having the user read of their card number either uniquely
identifies the account, or gives a small number of choices which
can probably be made unique by name or zip code. Some companies identify
accounts by address, where more than one account per address (which
includes apartment numbers) is unusual (e.g. electric and gas utilities).
[color=blue]
>Actually, the card is going to be assigned to a household (home
>address), so the combination of house number, street and zip is
>probably unique but using this as a primary key would make all queries
>quite cumbersome, compared to some id.[/color]

Consider that sometimes people WANT separate accounts. People in
process of a nasty divorce proceeding, a hundred college students
in the same dorm, everyone in Cell Block B, etc. may not want to
share the account.

How are you going to deal with conflicting requests from different
household members with respect to the account? E.g. wife keeps
cancelling it, teenage son keeps re-opening it.
[color=blue]
>Is this a case where an identity (aka auto_increment) is inevitable or
>even highly recommended?[/color]

Yes.
[color=blue]
>Another alternative I thought of was to use the card id as primary key,
>but have the same user record appear in the users table multiple times,
>but with a different card id, where each card id might actally be
>'blocked' or not. This blocked cards can then be deleted once the
>records in other tables that refer to that id are also deleted, such as
>logs. Sounds ugly as this introduces some redundancy of user records,
>but might actually be acceptable I think, because logs are deleted
>every now and then and lost cards will be rare. Not sure if I should
>design the database with the 'rule' or 'exception' in mind.[/color]

The card-to-account table works like this, except that all it
contains and duplicates is the account number, NOT account-specific
stuff like names, addresses, and such.
[color=blue]
>Yes, I'm a rookie.. and I don't mind being chewed out for it, as long
>as the lecture includes something useful I can work with and learn from[/color]

Gordon L. Burditt