On Mon, 10 Dec 2007 12:07:44 +0100, Kees Nuyt wrote:

> With person_id being the primary key in attribute you would only be able
> to insert one attribute row for every person row.

That's the problem.

> Also, person_id can't
> be auto_increment, it would probably refer to the wrong person.

Ok, I just quickly hacked the schema together, think of it as pseudo SQL.

> In order to store n attributes per person you have to revise your
> schema:
>
> -- no changes for person
> create table attribute (
> person_id int(11),
> attrib_id int(11),
> name varchar(50),
> PRIMARY KEY (person_id,attrib_id),
> INDEX idx_fk_pi (person_id),
> FOREIGN KEY(person_id) references person.id
> ) ENGINE=InnoDB;
>
> You could define attrib_id as auto_increment (in which case you have to
> index it), depending on your requirements there are other options.

So except for the attribute table now having a primary key, what's
different?

> With the changed schema your will problem will be a different one.

I don't see how that changes the problem.

-Samuel