Advice: Foreign Keys & Multiple List

[Anon]

hey there,

i need some advice.

have a situation where a Users table has a Category field, and users need to be placed in 1 to 50 different categories.

i wanted to foreign key the User's Category field to a Category table, but since each user will have multiple categories, i don't think i can use the mySQL foreign key features (which i find somewhat confusing anyway. innoDB??)

so i thought i would set it up like this:

Category field in Users table is CHAR and would contain comma separated ids (something like 12,15,75,...)

those numbers would correspond to an auto_increment unique key in the Category table other than the pri_key.

so, my questions are these:

1. am i correct is not using the Category table's pri_key? i had assumed that since there is no "internal connection" between the fields, when categories are deleted the pri_key field will renumber itself and all of my associations will be inaccurate.

2. what else might i need to keep in mind in order to ensure data integrity? and/or is there a better way to do this?

any advice would be appreciated,

mason

[Anon]

Do never store comma separated values in a column.

If you have a many to many relation you need an extra table to hold the relation

i.e.

create table users(userid int primary key, ...)

create table categoriers(categoryid int primary key)

create table userCategories(userid int ,
categoryid int , primary key(userid,categoryid),
foreign key(userid) references users(userid),
foreign key(categoryid) references categories(categoryid)

when categories are deleted the pri_key field will renumber itself >>

What do you mean by this? There is no automatic renumbering of identity values.

[Anon]

cool. thanks for the info.
i'd always wondered how to do that, and you answer illuminates the whole process.

the renumbering of the pri_key field was just an assumption. i take from your response that it was an incorrect assumption.

thanks again for you help,
mason°

[[deleted]]

Primary key values never change, because they identify your records. Once a PK value is assigned to a record, you must assume that there are other records that use this value to link to this record. If you change the value, the other records suddenly reference a different record, and all your data becomes just a bunch of inconsistent doody :-)