SitePoint Sponsor |
|
User Tag List
Results 1 to 5 of 5
-
Nov 7, 2009, 10:39 #1
- Join Date
- Sep 2003
- Location
- Northern California
- Posts
- 605
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
MySQL : adding fields to table : good normalization practice
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.
-
Nov 7, 2009, 12:20 #2
- Join Date
- Jul 2003
- Location
- Kent
- Posts
- 1,921
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
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.Dr John
www.kidneydialysis.org.uk
-
Nov 7, 2009, 12:49 #3
- Join Date
- Sep 2003
- Location
- Northern California
- Posts
- 605
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Nov 7, 2009, 13:40 #4
- Join Date
- Sep 2005
- Location
- Sydney, NSW, Australia
- Posts
- 16,875
- Mentioned
- 25 Post(s)
- Tagged
- 1 Thread(s)
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.Stephen J Chapman
javascriptexample.net, Book Reviews, follow me on Twitter
HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
<input name="html5" type="text" required pattern="^$">
-
Nov 7, 2009, 13:52 #5
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
Bookmarks