List:General Discussion« Previous MessageNext Message »
From:Rhino Date:March 8 2006 5:03am
Subject:Re: Table with multiple primary keys - How
View as plain text  
----- Original Message ----- 
From: "fbsd_user" <fbsd_user@stripped>
To: <mysql@stripped>
Sent: Tuesday, March 07, 2006 11:40 PM
Subject: Table with multiple primary keys - How


>
> What I am trying to do here is have 3 separate primary keys.
> Creating a mysql select on either of the Logon_id, email_addr,
> or last_name fields will do a single read to the matching value.
> Like having 3 different indexes into the same table.
> I don't want those 3 field concatenated together as a single key.
>
> Is this table definition correct?
>
By definition, a table cannot have more than one primary key. Period. Your 
definition will probably execute successfully - I haven't tried it so I 
can't be sure - but 'successful' only means that it will (probably) not fail 
on a syntax error. Your definition does NOT create three primary keys, it 
creates one key on the combination of three values. In other words, your 
definition concatenates the three keys together, which you say you don't 
want to do.

Your basic goal of having three separate primary keys is not possible. 
However, you _could_ create a primary key and two unique keys or three 
unique keys. But before you start doing that, I suggest you take a step back 
and rethink your design.

The proper way to design databases is to do logical design FIRST, then 
consider physical design. In other words, make sure that your design hangs 
together logically first and meets all your business requirements. Then, and 
only then, should you consider physical design, i.e. redesigning tables to 
optimize performance. Logical design is always done with the assumption that 
you are working on a perfect processor that has outstanding performance no 
matter what query you run. Once the logical design is perfect, you can start 
being realistic and modify your design to optimize performance for real 
world non-perfect processors.

I think you're leaping into physical design before you've finished logical 
design. You're worried about query performance before you've satisfied 
yourself that the logical design even works. You need to consider what 
primary key will support your logical design. Is the login_id alone 
sufficient to uniquely identify rows of the members table? Or do you need 
other columns to accomplish this? If, in fact, you need all three columns 
(login_id, email_addr, and last_name) to uniquely identify rows in the 
members table, then all three of those need to be in your single, 
concatenated key, exactly as you have it in your code.

Is it possible for more than one person to have the login_id ABC123? If not, 
the login_id by itself should suffice to be your primary key. In that case, 
you may want to make email_addr and last_name separate unique keys - or not. 
If the login_id is sufficient to uniquely identify a member that's fine; 
make login_id your primary key. But you may not want to make email_addr or 
last_name unique keys. What if two of your members have separate login_ids 
but share an email address? Making email_addr unique will prevent one of 
your two members from being added to the table. This is an even bigger 
problem for the last_name; if you make it unique, and you already have one 
Smith in the table, you will never be allowed to have another Smith in the 
table!

You need to sort out the issue of the primary key FIRST. Once that is 
resolved, you can start to think about making other keys unique or not. But 
right now, I think you're getting badly ahead of yourself.

> create table members (
>        logon_id            varchar(15),
>        email_addr          varchar(30),
>   last_name           varchar(30),
>   member_type         char(1),
>        email_verified      char(1),
>   logon_pw            varchar(15),
>   date_added          date,
>        last_login          timestamp,
>        first_name          varchar(30),
>        addr1               varchar(30),
>        addr2               varchar(30),
>        city                varchar(20),
>        state               varchar(20),
>        zip                 varchar(15),
>   phone_home          varchar(15),
>        phone_office        varchar(15),
>        phone_cell          varchar(15),
>        mothers_maiden_name varchar(30),
>   ip_of_useratsignup  varchar(16),
>   primary key(login_id, email_addr, last_name)
> );
>
--
Rhino 



-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.2.0/275 - Release Date: 06/03/2006

Thread
Table with multiple primary keys - Howfbsd_user8 Mar
  • Re: Table with multiple primary keys - HowDan Nelson8 Mar
  • Re: Table with multiple primary keys - HowRhino8 Mar
  • Re: Table with multiple primary keys - Howmysql8 Mar
    • RE: Table with multiple primary keys - Howfbsd_user8 Mar