MySQL : adding fields to table : good normalization practice

[mgm_03]

I have a table with the usual company information : name, address1, address2, city, state, zip, phone. Sometimes the physical location is different from the mailing address (e.g. po box). It seems to me that billing address info belongs in the same table.

I want to let the company add "tags" - like keywords - to describe their products or services. So, if a user searched tags for "widgets" - companies associated with that tag appear. To keep tables normalized, would it be practice to have the tags in a field in the company table.

[Dr John]

Create a separate table, called tags, with the fields tag and companyID as the joint primary field. you idea would probably not be normalised.

if you put all the tags in one field, searching for a given tag is trickier; if you had several tag columns, you'd have to search each column for each tag, so searching becomes difficult. With a separate table, you only search the one column for the tag(s) you want.

[mgm_03]

I didn't know you could use two columns together for a primary id. Whenever I had a many-to-many mapping table, I always had an auto-increment primary id.

[felgall]

You can have as m any fields in the key as you like.

If your primary key is field1 and field2 in that order then you effectively have two indexes one with both fields and one with just field1 while if you used an autoincrement you effectively don't have either of those indexes and things will probably run a lot slower.

[r937]

I didn't know you could use two columns together for a primary id. Whenever I had a many-to-many mapping table, I always had an auto-increment primary id.

quite useless in a many-to-many table

you'd want a unique constraint on the pair of FK columns in any case, to prevent the same company from getting the same tag more than once

and of course, one or two indexes on the FK columns as well, to optimize queries

you may note that any query referencing a many-to-many table never references its auto_increment value