Tables & Checkboxes

[RikForgo]

Soliciting a little advice here ...

We're setting up a click-through app where our users will name a record and then click through and select multiple categories per record to create the characteristics of that record.

For example, in the first page ('Category') the user would select one or more items (in this case 'Transportation' and 'Technology'). In turn, those choices would trigger a query based on Transportation and Technology that would give the user checkbox choices in the Subcategory field (where he selects 'Aircraft', ''GPS Receiver' and 'Video Camera'). Again, those choices would trigger the final checkbox field ('Type') where other choices could be made (example below).

CATEGORY:
Communications
Clothing
X - Technology
X - Transportation

SUBCATEGORY:
Car
Boat
Train
X - Aircraft
X - GPS Receiver
X - Video Camera

TYPE:
X - Helicopter
Commuter Plane
Commercial Jet
X - Computers
X - A/V

My question is, what is the best way to handle the table-CF structure here?

I envision CATEGORY, SUBCATEGORY and TYPE to be single columns within the same table (unless there's a reason not to do it that way). Every checked box would create a new record in the table.

If that sounds like a reasonable approach, I'd need to start foraging around to learn about how to make CF loop around and create a new record for each checkbox.

Has anyone done something like this before? Any advice is quite welcomed.

 

[r937]

some suggestions:

if you indeed want a category-subcategory structure, build up a hierarchy and enforce some consistency

for example, is Aircraft a subcategory of Technology?

what type of record would have a "type" that is both A/V and Helicopters? shouldn't Helicopters be a subcategory of transportation, not a type?

can a record be tied to a category and a subcategory at the same time? can it be tied to a type without a subcategory?

every checked box would create a new record in the table? so if Aircraft is a subcategory of Transportation, a record would be added twice?




rudy

http://r937.com/

SQL Consulting

 

[RikForgo]

Sorry, I stupidly rushed through some bogus examples because I'm somewhat restricted in what I can post. These aren't the actual items that would be checked, but I wanted to know if the structure made sense. Perhaps it doesn't. I didn't cut my teeth on data modeling so bear with me.

In our scenario any particular category, subcategory or type ('type' is actually 'items' in our case) can have multiple selections. I just want to know what's the best way to record them. Would it make more sense to have each checked selection recorded in a separate column field as opposed to an individual record?

 

[r937]

depends

are you going to tie an item to both a category and a subcategory? to more than one of each? to a subcategory but not to the subcategory's parent category? to multiple subcategories within the same category?

rudy

http://r937.com/

SQL Consulting

 

[RikForgo]

> are you going to tie an item to both a category and a
> subcategory?

No, items will be tied just to a subcategory.

> to more than one of each?

No, just the one subcategory (if that's what you mean).

> to a subcategory but not to the subcategory's parent
> category?

No, the category feeds the subcat, and the subcat feeds the item. There won't be any cross-pollination at the category level.

> to multiple subcategories within the same category?

Yes, that could happen.

 

[r937]

okay, next question, how married are you to checkboxes?

what about related dropdowns?

http://evolt.jeffhowden.com/jeff/code/related_selects.cfm

i guess it doesn't matter, because the database structure is pretty clear now

create table categories
( id smallint not null primary key
, descr varchar(50) not null
, parentid smallint null
);

each category has a null parentid, and each subcategory has the parentid of its parent category's id

create table itemcats
( catid smallint not null
, itemint smallint not null
, primary key (catid, itemid)
, foreign key catid
references categories (id)
, foreign key itemid
references items (id)
);

this is the "linking" table which relates items to categories

note that if you need to restrict that items be tied only to subcategories and not categories, you need to do that with application code prior to the insert

the primary key ensures that each item can be linked to a specific (sub)category at most once





rudy

http://r937.com/

SQL Consulting