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(birthdate)' 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(birthdate)
);

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]

"James Johnson" <[e-mail]> wrote in message
news:5h********************************@4ax.com...

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(birthdate)' 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(birthdate)
);

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

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]

On Thu, 23 Dec 2004 14:14:04 GMT, "Rich R" <rr***@cshore.com> wrote:

"James Johnson" <[e-mail]> wrote in message
news:5h********************************@4ax.com.. .

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(birthdate)' 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(birthdate)
);

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

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

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