Hi, I'm building a database for my site using MYSQL with Phpmyadmin/PHP on IIS. It's a very basic ecommerce site design. The database structure will look like this
one database with 4 table: 1. categories table. 2. product table. 3. order table. 4. orderdetails table.
very basic relation database. The categories table is for one-to-many, and the product table is for many-to-many with the category entity.
All table will start of with the top field reserver for the ID(primary key) for internal identification.
My question is.
Since I already have an ID field. Do I need a parent_id field in the categories table for it to work(query) with the product table. Or can I just create the categores table with something lookin like this.
ID Int null Primary Key CategoriesA Varchar CategoriesB Varchar CategoriesC Varchar
I think you just want a category_id field int he products table that stores which category the product belongs to. Then if you're displaying the product you also pull the category info using a join or by getting the id and pulling the category directly with your php. If you're displaying a category page, say for category 5 then you do a query on the category_id field in products to pull all products with category id 5.
I want to thank everyone for helping me. I want to thank Steele for showing me a much better design. I actually use it right now. I like to plan ahead rather than having to go back and redesign my database later on. so thank you very much guys, and sorry for not answering back right away.