How to automate table names.
Ok, so far, I've been naming tables after categories or subcategories...basically naming them so that they make sense. Now, I am faced with making the interface for the site, and I want users to be able to add sub categories, which is fine as far as making a new table using php in mysql,, but somehow I have to make a naming convention that will make sense and that works.
I thought maybe I could use the url variable for a subcategory name as a table name, but that won't work because some of the categories have spaces, so they have url encoding and are named similar like Paper Clips and Paper - where then both tables would be named paper because of the url encoding/spaces.
How does a site like monster commerce do it? Does it just make table names in sequence and associate them to the subcategories?
pr0gr4mm3r posted this at 01:58 — 19th December 2007.
He has: 1,502 posts
Joined: Sep 2006
Creating one table per category doesn't seem necessary. What is this site for?
L3ZL1E posted this at 20:36 — 19th December 2007.
They have: 32 posts
Joined: Feb 2007
There is one product table, with the basic product information in it, but the products can exist in more than one category/subcategory, therefore i made tables with product numbers to associate them with categories and subcategories because I really didn't know how many columns I would need if I went that route (they are organizing the information as I make the site). Seemed to be the most logical solution at the time. Maybe it was a bad idea???
pr0gr4mm3r posted this at 22:04 — 19th December 2007.
He has: 1,502 posts
Joined: Sep 2006
Basically, you're asking for a many-to-many relationship. Each item can have multiple categories, and each category can have multiple items. In databases, that it not possible without using an associative table. This is a table in between the products and category tables.
For example, you need a table that holds the category id and the item id. There will be an entry in this table for each item-category relationship. If you have the item with the id '354', and a category with the id '546', and they are associated with each other, then you enter a record in the associative table with both these ids. If this item is also part of category '547', then you put another record in the associative table for that as well.
If this associative table has the name "Item_Cat", and you want to find all the items in category 546, then you write a query like
SELECT * FROM Item_Cat WHERE category_id = '546';'You can easily join that with your items (table name: "Items") table by doing something like this:
SELECT * FROM Item_Cat INNER JOIN Items ON Item_Cat.item_id = Items.item_id WHERE Item_Cat.category_id = '546';'L3ZL1E posted this at 22:24 — 19th December 2007.
They have: 32 posts
Joined: Feb 2007
Ok, thanks, that makes sense. Then it would follow that they would then be sequentially numbered, as they are added-which would solve my dilemma.
I just like the fact that I can look at the table names in my database admin and know that is the category or subcategory I am dealing with-it's already very frusterating that they have been organizing the information as they go along...and I've had to tweak it as I go along...but I took on the project, so gotta just deal with it!
Thanks again! Much appreciated!
pankaj.sharma posted this at 06:21 — 5th January 2008.
He has: 6 posts
Joined: Dec 2007
Thanx for all you guys for this nice discussion
GMI: Web application Development India
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.