Hello:
I could use some help/advice on table design and possibly inserting into the table. I'm probably making what I need to do too complicated so I could use a second pair or third pair of eyes to point me in the right direction.
The tables are actually going to be designed for a physician's office but for ease of explanation I'm going to use products and categories.
This is the scenario:
All products are going to have a parent category.
Some products will be in a sub-category.
My problem is I can't seem to figure out how to make the association between parent category and sub-category. For example, we have the following:
Categories A and B are "parent" categories.
Category A has the following sub-categories:
A1, A2, A3, A4
Category B has the following sub-categories:
B1, B2, B3, B4
This is the road I was thinking of going.
First, I have a table called category_description which has two columns:
category_id and category_name. So, the table would look like:
cid cname
1 Root A
2 Sub A1
3 Sub A2
4 Root B
5 Sub B1
etc.
Second, I have a table which associates the parent category and subcategory. This is how the data in the table should appear:
cid pid
1 0
2 1
3 1
4 0
5 4
The parent id for all parent categories (A and 😎 are set to 0.
The parent id for the sub-categories are set to the category id of the parent category.
My first question, would this be the correct table design to properly setup categories and sub-categories?
My actual problem lies in inserting the data into the tables. I have a basic "Add Category" form which asks for the following:
Internal Category Code (input type="text")
Category Name (input type="text")
Parent Category (input type="??")(should be a lookup of categories which should also allow for a blank option)
The form field, Parent Category, should be the field which associates the sub-categories to the parent categories. I was thinking of creating the Parent Category form field as a lookup.
The form should behave as follows:
When the person adds a new category, if the form field, Parent Category, is left blank, the query insert into the parent_id field should be 0 indicating that it's the actual root category.
When the person adds a new category and they select a Parent Category from the lookup table, the query insert into the parent_id field should be the category_id of the of the root category. So, in our example:
category CAT A => cid = 1 and pid = 0
category SUB A1 => cid=2 and pid = 1
My problem:
How do I create the Parent Category form field so it behaves like a lookup? This field can contain a blank (which is needed for the root category) and the field should also show the categories so they can be properly selected.
How should my "INSERT" statement(s) appear so that everything is entered correctly into the appropriate tables? My primary concern is the association between the root category and sub-categories.
Thank you all for your time. I'll be happy to explain things further if needed. As I said, I'm probably making a mountain out of a mole hill. But I have drawn a complete blank on how to write my form for the "Parent Category" field which should act like a select and yet allow a blank to be an option. 🙂
