Is the structure of this example database ok?

[James Johnson]

I have a question about creating a simple database. Here are the 2 tables.
They are linked by the primary/foreign key 'client_id'. I think that I
understand that part.

I have 'KEY birthdate(birth date)' in the clients table definition. I'm not
sure when I should have that. When is it desirable to use the 'KEY' in the
definition? If I wanted to be able to often and quickly sort the clients table
by lastname, firstname, or birthdate, should I use the 'key' phrase? Ie, would
the clients table sort faster on birthdate than on lastname in this example?
If I wanted to be able to efficiently sort on all columns then should I have a
'key' phrase for each column in the table?



CREATE table clients (
client_id int(10) unsigned NOT NULL auto_increment,
birthdate date NOT NULL default '0000-00-00',
lastname char(15) NOT NULL,
firstname char(15) NOT NULL,
PRIMARY KEY (client_id),
KEY birthdate(birth date)
);

CREATE table sales (
saleno int(10) unsigned NOT NULL auto_increment,
client_id int(10) unsigned NOT NULL,
description char(15) not null,
amount float(10,2) NOT NULL,
PRIMARY KEY (saleno),
KEY amount(amount),
constraint foreign key (client_id) references clients (client_id)
);

thanks,
Jim

[Rich R]

Re: Is the structure of this example database ok?


"James Johnson" <jj@yaaho.com > wrote in message
news:5hfgs09j5o 4vfthmvdk2fqdgi hf8r4up3i@4ax.c om...[color=blue]
>
> I have a question about creating a simple database. Here are the 2 tables.
> They are linked by the primary/foreign key 'client_id'. I think that I
> understand that part.
>
> I have 'KEY birthdate(birth date)' in the clients table definition. I'm not
> sure when I should have that. When is it desirable to use the 'KEY' in the
> definition? If I wanted to be able to often and quickly sort the clients[/color]
table[color=blue]
> by lastname, firstname, or birthdate, should I use the 'key' phrase? Ie,[/color]
would[color=blue]
> the clients table sort faster on birthdate than on lastname in this[/color]
example?[color=blue]
> If I wanted to be able to efficiently sort on all columns then should I[/color]
have a[color=blue]
> 'key' phrase for each column in the table?
>
>
>
> CREATE table clients (
> client_id int(10) unsigned NOT NULL auto_increment,
> birthdate date NOT NULL default '0000-00-00',
> lastname char(15) NOT NULL,
> firstname char(15) NOT NULL,
> PRIMARY KEY (client_id),
> KEY birthdate(birth date)
> );
>
> CREATE table sales (
> saleno int(10) unsigned NOT NULL auto_increment,
> client_id int(10) unsigned NOT NULL,
> description char(15) not null,
> amount float(10,2) NOT NULL,
> PRIMARY KEY (saleno),
> KEY amount(amount),
> constraint foreign key (client_id) references clients (client_id)
> );
>
> thanks,
> Jim
>[/color]

Hi Jim,

This is a big issue. In general indexes make finding rows in your database
faster. So if you search on birthdate often you 'may' find performance
benefits. Always use EXPLAIN to see just what's going on. But you must read
chapter 7 of the manual to really understand what's happening.

BTW, the way you have your database structured a salesperson can only have
one client. You made it a primary key. Is this what you meant? Or is this
table a row for each client with sales info for each client?

Regards,
Rich

[James Johnson]

Re: Is the structure of this example database ok?

On Thu, 23 Dec 2004 14:14:04 GMT, "Rich R" <rryan@cshore.c om> wrote:
[color=blue]
>
>"James Johnson" <jj@yaaho.com > wrote in message
>news:5hfgs09j5 o4vfthmvdk2fqdg ihf8r4up3i@4ax. com...[color=green]
>>
>> I have a question about creating a simple database. Here are the 2 tables.
>> They are linked by the primary/foreign key 'client_id'. I think that I
>> understand that part.
>>
>> I have 'KEY birthdate(birth date)' in the clients table definition. I'm not
>> sure when I should have that. When is it desirable to use the 'KEY' in the
>> definition? If I wanted to be able to often and quickly sort the clients[/color]
>table[color=green]
>> by lastname, firstname, or birthdate, should I use the 'key' phrase? Ie,[/color]
>would[color=green]
>> the clients table sort faster on birthdate than on lastname in this[/color]
>example?[color=green]
>> If I wanted to be able to efficiently sort on all columns then should I[/color]
>have a[color=green]
>> 'key' phrase for each column in the table?
>>
>>
>>
>> CREATE table clients (
>> client_id int(10) unsigned NOT NULL auto_increment,
>> birthdate date NOT NULL default '0000-00-00',
>> lastname char(15) NOT NULL,
>> firstname char(15) NOT NULL,
>> PRIMARY KEY (client_id),
>> KEY birthdate(birth date)
>> );
>>
>> CREATE table sales (
>> saleno int(10) unsigned NOT NULL auto_increment,
>> client_id int(10) unsigned NOT NULL,
>> description char(15) not null,
>> amount float(10,2) NOT NULL,
>> PRIMARY KEY (saleno),
>> KEY amount(amount),
>> constraint foreign key (client_id) references clients (client_id)
>> );
>>
>> thanks,
>> Jim
>>[/color]
>
>Hi Jim,
>
>This is a big issue. In general indexes make finding rows in your database
>faster. So if you search on birthdate often you 'may' find performance
>benefits. Always use EXPLAIN to see just what's going on. But you must read
>chapter 7 of the manual to really understand what's happening.
>
>BTW, the way you have your database structured a salesperson can only have
>one client. You made it a primary key. Is this what you meant? Or is this
>table a row for each client with sales info for each client?
>
>Regards,
>Rich
>[/color]


I thought that I had a sales table that can only have one client. Ie,
one sale to one client at a time? Isn't that correct? That's what I'm
trying to structure. I can't think of a need for a sale to have more
than one client.

Jim