Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Other Forums
 MS Access
 generic way to display tables with diff. columns?

Author  Topic 

ToddW
Starting Member

4 Posts
Posted - 2005-01-08 : 12:47:24
I have a client who wants to put their catalog online. They have different Catagories (ex: Tape, Paper, Stretchwrap) with many Sub-Categories per Catagory (ex: Tape has Security Gummed, Natural Gummed, etc).

The problem is that each of these Sub-Categories can have different columns. :-(

EX:
Security Gummed : Item#, printed message, size, mil, rolls/case
Naturally Gummed: Item#, width, length, strands, rolls/case, color

I would like to have a generic ASP page that would be linked to from a Product Menu that could display which ever Category/Sub-Category the user chooses and display the appropriate columns and column names.

Anyone have any thoughts on how to define the table(s) to do this?

Thanks in advance for any help :-)

jhermiz

3564 Posts
Posted - 2005-01-08 : 13:07:35
Hey Todd,

Although it may seem like seperate sub categories it really is not from a table perspective.
You will need a table which links back to itself for instance you mentioned:


Tape
   Security Gummed 
   Natural Gummed

In a table valid records would be

CategoryID   ParentID  Product
 1             NULL        Tape
 2             1        Security Gummed
 3             1        Natural Gummed
....


What you have here is the type of product and it's parent. Some products (the main categories) will
not have a parent associated with them. From a tree perspective, these are your "root pointers" they are
at the highest level in the hierarchy. Below the root may be various subcategories...and you can take it further
than that. You may have a case where a sub category branches off into many different other sub sub categories.

SubCategories
-------------
CategoryID (ID)
ParentID (allow nulls, may be a foreign key from another table - Category)
SubCategory (the sub category itself)

The parentID is just the key of the parent if one exists

1 NULL Tape (Tape in a Category table will be some sort of ID number say 1)

If a subcategory has a parent you reference that ID
number and you enter the sub category.

If you wanted all categories you could do:


SELECT SubCategory FROM SubCategory WHERE ParentID IS NULL


If you wanted all sub categories:


SELECT SubCategory FROM SubCategory WHERE ParentID IS NOT NULL


If you wanted a subcategory for a specific category, for instance you wanted all sub categories of the category tape:

Assume that tape is in the Cateogry table with CategoryID number = 1


--this is in the case that you store the main categories in another table
SELECT SubCategory.SubCategory FROM SubCategory INNER JOIN Category ON
Category.CategoryID = SubCategory.ParentID
WHERE SubCategory.ParentID= 1


This should return:
Security Gummed and Natural Gummed provided you have entered these into the table correctly:


CategroyID        ParentID  SubCategory
                  --------  ------------
     1              NULL        Tape
     2               1           Security Gummed
     3               1           Natural Gummed


Make sense ?

Jon

[edit]
You have the option of placing the main categories in a seperate table if you want. You can also just keep the main categories in this table and label the ParentID as NULL. If you take this route and you wanted all tape subcategories:


SELECT SubCategory FROM SubCategory WHERE ParentID=1


Sorry I had forgotten to mention this from before. So it is upto you whether you need to seperate your main categories from your sub categories. If more information is needed for a particular category than I would normalize it and place it in a seperate table from your sub categories. But if you have no information that you need to store than you could get away with storing it in a single self referencing table.

[/Edit]



Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Go to Top of Page

ToddW
Starting Member

4 Posts
Posted - 2005-01-08 : 13:49:58
Hi Jon,

That was quick. Thanks for the reply.

Your post cues me in on some things I need to learn but not sure it solves my problem - but I may not understand it.

See if this gives you a more information on where I am and what I need (or think I need :-)

My initial thoughts were:

Have a Category table (Tape, Stretchwrap, Paper, etc) with CategoryID and CategoryName as the 2 columns (I could use this to build a dynamic Product Category menu).

Have a SubCategory table (Security Gummed, Natural Gummed, etc) with SubCategoryId, Name and CategoryID (with Referential Integrety enabled so they can't accidentally delete a Category that has a SubCategory). I could use this to build dynamic SubCatgeory fly-out menus.

Have an Item table which would have CategoryID, SubCategoryID (with Referential Integrity enabled), and appropriate columns as part of the table.

My problem is with the Item table. The items have different attributes (some have Width & Length others have Strands & Rolls per Case). There are 32 possible columns to cover all the items and each item uses some combination of 4-6 of these.

Should I put all these in one table and just leave columns null that aren't used? If so, how can the page displaying the data figure out what columns are to be displayed and what the column names are?

Is this possible?

Should they all be in separate tables? Still, how could 1 generic page figure out how many columns there are and what the column names are?

Thank so much for helping.

Todd


That's my conundrum.
Go to Top of Page

jhermiz

3564 Posts
Posted - 2005-01-08 : 16:53:05
Ack slow down a bit :)

I dont think you need 32 column names and having these as bits checked on our off if it is valid or not.

Take a step back and read what I posted. I think you need a table that within this table..references itself.

Todd, let me give you an example. Lets say, and I am just guessing here so you can modify to fit your needs, you have a category called cars. Cars can come in all sorts of colors BUT there are specific cars that have 4 wheels as opposed to 3 wheels, some cars have abs others dont, some cars have a stick shift, others dont.

So lets look at this in a self referencing table:


CategoryID   ParentID     Item
-------------------------------
1             NULL        Car
2             1           Volvo
3             1           Mustang
4             2           ABS
5             2           Air Bags
6             2           AWD
7             3           Stick Shift
8             3           Mach stereo
9             3           Air Bags
------------------------------------------


What do you see from this. We see a root node called "Car" it branches off into two cars, a Volvo (id=2) and a Mustang (id=3). A volvo and a mustang have different features, sure some features may be the same (they both have air bags, see the table I posted). But a lot of the features cannot be found in common with both vehicles. For instance, notice the volvo has ABS but the Mustang doesnt. Furthermore the mustang has a mach stereo system and the volvo doesnt.

My point here is you do not need a table with 'n' columns where n will probably grow...For instance, today you said that there may be 32 columns of things where an item has something while another item might not have that same thing. What happens when your boss says well item x can have attribute y now...and you never defined y ??? You will have to keep putting bandages over a table that is growing constantly.

In fact the worst thing to do is to create 32 bit columns in a table and check off what item has what. It is painful, the size of your row grows, updates can be difficult, and its just a lot of work when new attributes are added.

Think about my example and tell me what you think. You can even create another sub level table that stores the various categories...

Either way using that table I posted If I wanted all the cars I could do:


SELECT Item FROM YourTable WHERE ParentID=1



result...
Volvo
Mustang


If I wanted all the attributes of a volvo:

[code]
SELECT Item FROM YourTable WHERE ParentID=2


[code]
result set...
Volvo
------
ABS
AWD
Air Bags


I can do the same thing for the mustang...and so on and so on.

Jon


Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Go to Top of Page

ToddW
Starting Member

4 Posts
Posted - 2005-01-08 : 18:08:25
Jon,

Thanks for your patience, this way of doing it is new to me (as you may have guessed, I'm a multiple tables w/Joins kinda guy).

I'm starting to see. Very creative. I think with one last piece it may be solved. The individual item level.

Using your example, let's say this company is an Auto Parts Dealer and they sell Air Bags (category) and have air bags for Volvos and Mustangs (sub-category) and they have 3 types of air bags for the Volvo and 3 different types of air bags for the Volvo AND these air bags have different properites (the volvo has item#, width, depth and wheel size and the Mustang has item#, #of screws and diameter). How would that get into the database?

Example Result set for display:

Air Bags
- Volvo
- Item#, width, depth, wheel size
- 12543, 12, 4, 20
- 12567, 12, 4.5, 20
- 12789, 14, 5, 23
- Mustang
- Item#, #of screws, diameter
- 14367, 4, 30
- 14368, 5, 32
- 14369, 5, 34

Note: and they need to be sorted in ascending order for the appropriate columns for each type of item within the sub-category. How to I specify THAT! argh!

It may be less confusing to take a look at the actual site and use real examples if you don't mind: [url]www.pacnetusa.com[/url]

The Tape & Strapping categories have the most sub-categories.

I was originally thinking to have each sub-category on it's own page (when I was in the dark ages with mulitple tables :-) and the call to the ASP page could specify certain things (ex: list.asp?cID=1,scID=3,sort=x) but it would be nice to keep them on one page as it currently is.

Thanks again for your time,

Todd
Go to Top of Page

jhermiz

3564 Posts
Posted - 2005-01-08 : 21:19:48
Sorry for taking long to get back to you Todd.

I agree you should not have this on different pages, it should be all on one page. In fact you could if you wanted to put all sub categories in a drop down. You select a sub category and it can display its relevant children.

Please do not think you are 100% wrong in creating multiple tables, you could do that but your work may easily increase since you are tapping more tables etc...

As for your question of how data would go into a database, I will refer back to the referencing table itself. Again using the example like I had done from before lets take your data and enter it into my table


CategoryID     ParentID       Item         Description
--------------------------------------------------------------
1               NULL         Air Bags         Main Item
2                1             Volvo          Car
3                1             Mustang        Car
4                2             12543          Item #
5                2             12567          Item #
6                3             14367          Item #
7                3             14368          Item #
8                4             12             Width
9                4             4              Depth
10               4             20             Size
..........
........
.......


Now look at this carefully...it really becomes a tree with the root node (Air bags) at the highest level (no parent). Below the air bag we branch into the various cars that have air bags (Mustang, Volvo, BTW it is amazing that I was guessing in my previous post about it being a car type business and it ends up being just that). Below the vehicle we have the items that go with the volvo...


12543
...
..


And the items that go with the mustang

14367
....
...


Below is a split about the attributes of the item (the width, the depth, the wheel size). The great thing about this structure is you can go as far down the level as you want. In fact you can have the volvo have 20 item attributes while the
mustang may only have 3.

It may help to grab paper and pencil and just sketch the tree out or the rows and point them back to one another you will notice the following:

20, 4, 20 (width, depth, size)---> 12543 (the item) ----> Volvo (the vehicle) ----> Air Bags (the parent)

That is all those attributes lead all the way back to Air Bags, the parent. Sorting is simple as well, I am not too sure how you want it sorted but you can either sort based on the CategoryID, the ParentID, or your own sort by defining a new field to sort on.

I did a system very very similiar to the one you have described except it was in SQL Server. See if this SQL makes any sense:


CREATE PROCEDURE select_components_by_conveyor_type @CategoryID bigint
AS
BEGIN
SET NOCOUNT ON
DECLARE @reached TABLE (category bigint, subcategory bigint NOT NULL, UNIQUE(category, subcategory))
INSERT INTO @reached VALUES (NULL, @CategoryID)

   WHILE( @@rowcount > 0) BEGIN
   		INSERT INTO @reached (category, subcategory)
		SELECT DISTINCT Categories.ParentID, Categories.CategoryID 
			FROM Categories 
			JOIN @reached AS r 
			ON r.subcategory = Categories.ParentID 
			WHERE not exists (select * from @reached r where Categories.CategoryID = r.subcategory and r.Category = Categories.ParentID)

   END

SELECT DISTINCT Components.ComponentID, Components.Component, Components.EnglishDescription, Components.GermanDescription, Components.AdditionalInfo
FROM Components JOIN CategoriesByComponentType ON Components.ComponentID = CategoriesByComponentType.ComponentID
JOIN @reached AS r ON CategoriesByComponentType.CategoryID= r.subcategory ORDER BY Components.Component
SET NOCOUNT OFF
END
GO


This was using a very similiar if not identical (minus the names of the columns) table structure. What it did was it would take in an ID and find all sub levels below it. For instance let us say we have 3-4 combo boxes. The user first selects "Air Bags" I then take that result and pass the ID to the above procedure. What the procedure does is scan the table and find all sub categories of air bags. The sub categories would be placed in the second combo box. These sub categories in this case are:

Volvo and Mustang.

The user would then select the Volvo or the Mustang (lets say they choose Volve) I would take the ID of that volvo and pass it to the procedure. In return I would get all Items for a volvo. In your case the items 12543,12567,12789. In the third combo box the user would have to select one of these items...doing so would bring back the width, depth, and wheel size.

The solution would work for you with a minor bit of effort on your end. All it takes is just rereading these posts and coming up with a similiar schema. As I said you may be able to create another table if you'd like to store ALL sub categories and rather than having "Volvo" or "Mustang" in this items table you would have corresponding ID numbers. In this case you will have to do one more join to this table to grab the text.

Really slick solution.

I'm done for the night, I hope this made sense. Don't be scared to ask more questions :).

Jon


Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2005-01-09 : 08:40:27
am i completly missing the point or would all of this be very simpler with many to many relation between Category and Sub-Category. No need for parent child stuff just simple joins.
Category -< CategoryToSubCategory >- SubCategory


Go with the flow & have fun! Else fight the flow
Go to Top of Page

jhermiz

3564 Posts
Posted - 2005-01-09 : 12:34:23
What about items that dont have certain attributes?

I thought the self referencing table would be simplier :)

I think you can get away with either, but I found the self referencing a lot simplier, especially when you have a structure
that sometimes needs a column while other times doesnt.



Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2005-01-09 : 13:04:03
items that don't have attribs are not in the middle relation table.

Go with the flow & have fun! Else fight the flow
Go to Top of Page

jhermiz

3564 Posts
Posted - 2005-01-09 : 16:32:36
Todd,

You could do that as well

Categories
----------
CategoryID
Category

SubCategories
-------------
SubCategoryID
SubCategory

CategorySubCategories
---------------------
CategoryID
SubCategoryID

The only problem I see with this is a sub sub category :)...how far is too far ?
Meaning in your example an item (which is a sub category) may have sub categories beneath that.
If you use this approach you would have to make the item a category as well.

Jon


Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Go to Top of Page

ToddW
Starting Member

4 Posts
Posted - 2005-01-09 : 17:17:44
Hi Jon and spirit1,

Thanks for your responses to my thread.

Jon - if you check out [url]www.pacnetusa.com[/url], you'll see that an item is the lowest level.

Example:
Tape
- Reinforced Gummed
-- item# tap1000
-- item# tap1001
-- etc..
- Natural Gummed
-- item# tap2001
-- etc...

Your solution was creative and I understand what you idea was - basically a row for every category name, then sub category name and then for every attribute of every item. Then link them together with parent pointers (so-to-speak). I would have to enforce my own referential integrity which would is not a problem.

Spirit1 - My original thought was to have separate tables but I don't see any way to deal with my original problem that sub-categories under a category have different attributes. I could have separate tables for every sub-category but still don't know how 1 generic page could figure out how to display all the different tables. Or 1 big table of items with 32 columns (the total # of different attributes) but how would I figure out which columns are used and as Jon pointed out if a new item is added with a new attribute I would have to update the table to add the new column.

If you could give me an example of your idea - using the Tape page of the [url]www.pacnetusa.com[/url] website - that would really help. I'm not sure if I'm having a brain cramp or what.

Can't thank you guys enough,

Todd
Go to Top of Page

jhermiz

3564 Posts
Posted - 2005-01-09 : 18:53:44
Hi Todd,

It comes back to the table that references itself :)...

However, you can still get away with the following:

Categories
----------
CategoryID
Category

SubCategories
-------------
SubCategoryID
SubCategory

CategorySubCategories
---------------------
CategorySubCategoryID
CategoryID NOT NULL
SubCategoryID NOT NULL

Attributes
-----------
AttributeID
Attribute

CategorySubCategoryAttributes
-------------------------------
CategorySubCategoryID
AttributeID

Category stores your main category. Subcategory table stores all sub categories. CategorySubCategories stores the relation of a subcategory to a main category. Note the ID in this table may not be needed, but you will then have to include both these fields in CategorySubCategoryAttributes table if you do not store a key for this. Attributes will store every attribute in the db (the ID is optional, you could make Attribute unique, however I noticed this is access, and I know how the access folks like ID's :)). The final table stores the key row of CategorySubCategory and the corresponding attribute associated with it. This is a one to many relationship storing the categorysubcategory with all of its attributes. Would work fine....

Jon


Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2005-01-10 : 03:33:27
well basicaly jon beat me to it :) twice...

Go with the flow & have fun! Else fight the flow
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site