Re: Unique Indexes

On Sat, Mar 12, 2005 at 10:41:08 +0000,
ILove TheSpam <ilovethespam(at)hotmail(dot)com> wrote:
> Lets say I have 3 tables:
>
> surnames - surnameid serial (Primary Key), surname varchar (Unique)
> firstnames - firstnameid serial (Primary Key), firstname varchar (Unique)
> users - userid serial (Primary Key), firstnameid, surnameid

This is normally a bad design. Why are you doing this?
If these are real people's names, you don't want enforce that there aren't
two people with the same name unless you have some way to keep such
combinations of people out of your problem (perhaps by making them change
their names?).

There doesn't seem to be any point to the firstname and surnames tables.
If you want this for searching, nonunique indexes on the firstnameid and
surnameid fields of the users table should work.

Normally you would do this with a single user table that has an artificial
primary key to identify unique people, and then information about their
name and maybe some other properties of people. Checking for duplicates
may be hard, because it can be hard in real like to tell if two people
with the same same are really two people.