Toggle Buttons

[Morgana]

The problem with the buttons approach is that it requires a lot more code than combos/listboxes would require.

Thanks, Pat. Looks like I get to delve into combos/listboxes.

 

[Pat Hartman]

Start with two tables to define Category and SubCategory and we'll go from there.

 

[Morgana]

Start with two tables to define Category and SubCategory and we'll go from there.

So would this be Category-Table with Milk, Energy, Coca-Cola etc.
SubCategory-Table with Yup, Dunkin Donuts, Monster, Nos, Coke, MexiCoke, etc

I thought making a table with one field in it was not good normalization practice.

 

[arnelgp]

you don't need a second table.
create a BOM-like table.

 

[Morgana]

you don't need a second table.
create a BOM-like table.

Arnelgp, unfortunately, I'm getting an error on your attachment. BOM=Bill of Materials?

 

[Pat Hartman]

I thought making a table with one field in it was not good normalization practice.

We do have someone here who posts that idea regularly but I'm not sure what the alternative is. You need to define the categories, your other option is to hard-code them into a value list. That makes them part of the design and not data. As you might imagine, I disagree completely with the opinion that a table with a single field or a PK and a text value is not valid.

I typically would use an autonumberID for each table. That allows the text value to change without having to update all related records.

tblCategory
CategoryID (autonumber PK)
Category

tblSubCategory
SubCategoryID (autonumber PK)
CategoryID (FK to tblCategory and first field of unique Index)
SubCategory (second field of unique index)

The unique index for the second table limits a SubCategory from occurring more than once per Category. The autonumber PK cannot enforce business rules like this. I'm not sure what this app is all about but it is certainly feasible for a SubCategory to be assigned to multiple Categories. For example some tea with extra caffeine might also be a stimulant.

 

[arnelgp]

like i said you don't need another table.
you can do it in a single table.

what if you have sub, sub, sub-category? you will make 3 more tables?
you only need 1.

i am attaching it again.

 

[Morgana]

We do have someone here who posts that idea regularly but I'm not sure what the alternative is. You need to define the categories, your other option is to hard-code them into a value list. That makes them part of the design and not data. As you might imagine, I disagree completely with the opinion that a table with a single field or a PK and a text value is not valid.

I typically would use an autonumberID for each table. That allows the text value to change without having to update all related records.

tblCategory
CategoryID (autonumber PK)
Category

tblSubCategory
SubCategoryID (autonumber PK)
CategoryID (FK to tblCategory and first field of unique Index)
SubCategory (second field of unique index)

The unique index for the second table limits a SubCategory from occurring more than once per Category. The autonumber PK cannot enforce business rules like this. I'm not sure what this app is all about but it is certainly feasible for a SubCategory to be assigned to multiple Categories. For example some tea with extra caffeine might also be a stimulant.

Thanks, Pat. This makes sense to me as I originally had Branding (Category) and Location in their own tables, but was trying to change that to put each under the Product Table. I like this idea much better if they do need to change.

 

[Morgana]

The unique index for the second table limits a SubCategory from occurring more than once per Category. The autonumber PK cannot enforce business rules like this. I'm not sure what this app is all about but it is certainly feasible for a SubCategory to be assigned to multiple Categories. For example some tea with extra caffeine might also be a stimulant.

For this db, I am solely looking at the branding, ie: sweatshirt with Coke, backpack with Reign, umbrella with Topo Chico, etc. I originally had the main categories for the toggle buttons as what I thought would be a shortcut to choosing "like" products (Coke, Diet Coke, Caffeine Free Coke, Coke with Coffee = all would be under Coke). If I understand what you are saying, and what I need, I believe I could just have the SubCategory as the only table. I'm currently using an Excel spreadsheet which tracks all I need for the on-hand inventory part, it just doesn't go into the Employee/Customer/Reason parts I need to track, and for popular items, I'm getting up to column BA+ as each request has it's own column.

 

[Pat Hartman]

If you have a different table that already exists that defines a brand, you don't need to create a separate "category" table. The existing table will suffice.