ideas for schema

[Ninjakreborn]

I have already planned most of my database, but an entire, and very important section of the site, hasn't been planned yet. I am trying to set up something for categories, an idea for a database schema.  I have a category, sub-category, and different settings for different types of posts.  Meaning there are different things they input based on which category/sub-category they selected when posting an ad.  It also shows different things when they are viewed, depending on which ones.  Like they have a category, a sub-category, but each sub-category will need to accept different things.
The thing I am trying to do, like for instance the category is items, the sub-category for instance would be


[b]Items[/b]
Couches
Beds
Desks
Refrigerators
Microwaves
TVs
Air Conditioners
Posters/Paintings
Drawers/Dressers
Chairs
CDs/DVDs
Household
Electronics
Clothing/Jewelry

See for items category they are all the same
like
o Item Title (Adjacent one line text box)
o Price (Adjacent one line text box)
o Description (Large text box below)

that will cover 2 categories.

Then I have this setup, for instance once category is

[b]Academics[/b]
Textbooks
Notes
Cheat Sheets
Now the category, then the sub-categories.
The textbooks will need information like
o Course number (Adjacent one line text box)
o Title of book (Adjacent one line text box)
o Price (Adjacent one line text box)

Then the notes/cheat sheets will need
o Item Title (Adjacent one line text box)
o Description (Large text box below)


You see, so different categories/subcategories need different things, and on some of them there might be a category, then each sub-category is going to need information, I am having a hard time coming up with a good database schema, can someone offer some advive, personal input something like that.
I am thinking about
1. individual tables for each thing I need
2. Making one big table that will have
category
subcategory
then all the fields there, and it will only fill in the related fields, and only pull the related fields(I think this will be too hard to manage/create),
I have to do this to where I can still update it, and change things around later, maybe add more later, or refine them, or change them around, Ihave to start this system off right int he planning stage before I can start building it.

Sporting Goods
Computer/Accessories
Bikes
Collectibles

[b]Events[/b]
Tickets
Concerts
Sports
Clubs
Parties
Charity Events
Bar specials
Poker Tournaments

[b]Lost and Found[/b]
Items found
Items lost

[b]Rentals[/b]
Sublets
Parking

[effigy]

http://www.phpfreaks.com/forums/index.php/topic,106760.msg427294.html

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

[Ninjakreborn]

I am adding that to my bag of possibilities on this, but I am still looking for further advice/input, that is on my list, but I am wanting to create a different system somehow, something simpler to manage, simpler mantain, and definitely simpler to impliment.  I am thinking of 1 table for reach thing, and just linking them by id number, along the similar lines of what you showed me, just different somehow, any other advice, personal experiences, or ideas are appreciated.

[fenway]

Just keep the categorization separate from the data, and then you can do whatever you want with it later.  Even if you go the simple parent-child route, as long as you have a reasonable number of levels, even JOINs will solve your problem.

[Ninjakreborn]

I am thinking of this so far.
a list of categories on one table
a list of sub-categories on another table, but have the id for the cateogry affiliated with the sub-categories.
for instance I have all the sub-categories in one table, then have a place for the category id, which is hte category that sub-category falls under.  It'll get pretty hairy though, because from there, i have to figure out hte rest of the schema, so still open to any suggestions, personal experiences, opinions, ex-cetera.

[fenway]

Just have a single table of categories, child and parent; have the root categories with a NULL parent, and you're done.

[Ninjakreborn]

It's not that, it's that each of the sections have to be different.  Like each category has a sub-category, but some sub-categories have different data than others, some have different informaiton they need than others, this is what is slowing me down.

[Ninjakreborn]

Ok
here is what I have, I just want input/advice on how well you think it'll be to mantain
ok first off, as I said, I have categories, sub-categories, then those sub-categories sometimes have sub-sub categories, each sometimes needing different information for them.

ok here is hte current plan I have for my schema, I just have to write up the specifics for it

[b]Table Number 1[/b]
This will have by category id.  I will have the name of the category and category id

[b]Table Number 2[/b]
I will have a sub-category name, sub-category id, and the category id from the category it is associated with.

[b]Table Number 3[/b]This contains all the sub-sub categories.  It has the sub-sub category name, the sub-sub category id, the sub-category, and category id's associated with it. 

[b]Other tables[/b]
I have 1 different table for each type of specs. Like I might have a table that has
type
name
description
then one that has
price
type
description
address
whatever, then in those tables have the cat, sub-cat, sub-sub-cat, associated with it

All of this here is making sense but it seems to be getting a little too hairy near the end, any advice?

[AndyB]

[quote author=businessman332211 link=topic=107360.msg430749#msg430749 date=1157736067]All of this here is making sense but it seems to be getting a little too hairy near the end, any advice?
[/quote]

#1 Figure out the requirements BEFORE you start defining schema.  The sub-sub-category is a newly-revealed requirement.

#2 Pay careful attention to advice you get from experts like Fenway.

[Ninjakreborn]

[quote]Just have a single table of categories, child and parent; have the root categories with a NULL parent, and you're done.[/quote]
I didn't understand that, can you re-explain it

Does it have to do with something similar with what I said I had.
Also This was a requirement I just didn't know how to explain it first, this is a ad system, and I know all the requirements, but since I have been planning I am encountering, and thinking, since I took all of your advice on this planning, it's making a world of wonders, I feel after I am done planning totally, I could code/program it in a few days, because I can program lightning fast, what slows me down is when I start to have a problem, and have to learn something, or spend a few days thinking through something, that is when it becomes very very time consuming.  This is going to change the way I run my career forever.

[Zane]

I think fenway means to put all the categories whether a sub cat or not into the same table
and have a column with it's parent category

something like
catID  |      catName      |  catParent  |    specType
  0            academics                        root              regItem
  1            anAcademicsubCat            academics              regItem
  2            aSubSub                        anAcademicsubCat              regItem

I hope that makes sense

[Ninjakreborn]

I am still not grasping how to add in the last part, if it was just category, and sub-category, with all the same information It would be easy to me, but because I have to creaet sub-sub categories for some of the categories, along with each sub-sub category or sub-category has to be ablet o hold information it's making my head spin, I have been brainstorming, I have planned out most of the site, almost all the systems, and programming ideas, so I know everything I need to do, I haven't planned 2 things, this schema, working it within the site, and how I am going to allow them to browse, along with about 100 permission situations I have to create, which is fine, once I get past the schema things will start rolling forward from there.  I don't understand fully?

[fenway]

OK... here we go.  When you're relating categories to their parents, it makes absolutely no difference what "type of information" in in these categories... who cares?  You're simply relating two UID, one of the child category, one of the parent category.  You can do this recursively forever, so it doesn't matter how many levels down you go.  As far as category-specific information, break that out into another table.

Think really long & hard about this scenario before you post back... none of the additional information you have provided thus far has any bearing on programming this type of solution.  AndyB is right -- try to take it all in first.  You won't figure out anything if you're going to rush through it.

[Ninjakreborn]

Ok so like

CatID        SubCatID        SubSubCatID
1                  item
shit I am really, really confused.  I think I understand part of what you are saying

so
shit.  Totally lost.

[Ninjakreborn]

If you mean relating categories, and sub-categories based on id, I knew how to do that, if that's what you mean.
but you are saying instead of 1 table with category, then another with sub-category and relate them, but you are saying all in one table.
I am picturing something liek
TABLE-
name- categoryinformation
id          category            subcategoryid
Then have the names and id's  ???
Very confused, I am trying to take all of this in, I know most of mysql, I know about joins, I know about relational databases, and I know all about queries, but I don't understand the format you mean, it sounds like with your idea, I would have an easier time, managing categories, sub-categories, sub-sub-categories, and sub-sub-category specific information where applicable, but I don't understand the theory(or how to do) what you said, it's got me very confused.  It makes sense as in putting the category and sub-category into the same table, but I don't understand
I do understand the parent/child relationship theory from oop, and javascript, but not how it relates to this:S

[Zane]

when you're laying this DB structure out

just completely get the word sub categories out of your head
all you need to think about is that

every category has a parent

[quote=fenway]
You're simply relating two UID, one of the child category, one of the parent category.  You can do this recursively forever, so it doesn't matter how many levels down you go.
[/quote]
exactly

for instance you have

category 1
category 2
category 3
category 4
category 5

in another column you have their parents
so if category 5 is a parent of category 3.....
it's  a 'sub-category' of 5

[fenway]

Well, at least I'm getting through to someone... ignoring the PK for the moment, this lookup table simply has two columns, parent_category_uid, and child_category_uid:

So if we have a Fruits [1] category, which contains apples [2], oranges [3], and berries [4], when then contain, say, granny smith apples [5], strawberries [6] and blueberries [7], we'd have a categories table with one entry each (UID, name, etc).  I've enclosed the arbitrary UIDs in square brackets above for simplicity.

So our categories table would have the following records:

child_category_uid---parent_category_uid
1---NULL
2---1
3---1
4---1
5---2
6---4
7---4

If that doesn't make sense, I really can't help you out.  I can't make it any simpler.  Sorry.

[Ninjakreborn]

Ok, I have an idea of what you mean, I also found out it's only a category/subcategory/information
there are no 2 levels deep.  here is what I am wondering, is this correct, is this what you mean.

catid        subcatid
item        couches
item        beds
item        desks
item        refrigerator
event      tickets
event      concerts
event      clubs
event      sports
event      parties
event      charity events
rental      sublets
rental      parking
Then I can continue so on and so forth, is that what you are talking about, the only thing I didn't get was the null value.  If this is what you meant, I like the idea so far, then I can just have the sub-category specific information, each set in a different table, then have that table reference the category and sub-category.or something similar, then a master table I guess for posts, that will have
damn.  Ok I understand how to fashion my categories, my sub-categories.  I understand how to set up specific information, now what do I do about the posts.  When someone posts something they choose category, sub-category, based on the sub-category they enter the necessary information.  Then how to do I get that information together in a post, if someone can see where I am coming from.       

[fenway]

1) This is still backwards -- you want to set the parent, not the child.  That's what the NULL is about.  And it doesn't matter how many levels there are.
2) Those aren't UIDs.
3) You simply link the post with a particular category UID.
4) Traversing this table is, of course, the actual work... try and see what you can come up with.

[Ninjakreborn]

After a lot of thinking, a lot of planning I created something I can do the programming with pretty easily, and it's a clean system I can update, and change around easily later on

Tables relating to Posts
categoryinformation
Categoryname
Subcategoryname


SETS
In this website I am using sets as a basis for the posts.  A general set is a group of related information, and different categories may be set to different sets, it’s the set name, the fields needed within the set, at the bottom it is a list of what things go with what sets.  In case if anyone but me ever works on this website, in the future.

postset1
Related subcats- everything under the item and event categories
Categoryname
Subcategoryname
Userid
Postid
Item Title
Price
Description

postset2
Related subcats- lost items only under found category
Categoryname
Subcategoryname
Userid
Postid
Item Title
Reward
Description
postset3
Related subcats- found items only under found category
Categoryname
Subcategoryname
Userid
Postid
Item Title
Description

Postset4
Related subcats- everything under the rental category
Categoryname
Subcategoryname
Userid
Postid
ItemTitle
Price
NumberOfUnits
Description

Postset5
Related subcats- Everything under the work offered BY students category
Categoryname
Subcategoryname
Userid
Postid
Item Title
Wage
Description

Postset6
Related subcats- everything under the work offered TO students category
Categoryname
Subcategoryname
Userid
Postid
ItemTitle
Wage
HoursPerWeek
Description

Postset7
Categoryname
Subcategoryname
Userid
Postid
Course number
Title
Author
Price

[Zane]

yeah this is backwards...as fenway said
[QUOTE]
catid        subcatid
item        couches
item        beds
item        desks
item        refrigerator
event      tickets
event      concerts
event      clubs
event      sports
event      parties
event      charity events
rental      sublets
rental      parking
[/QUOTE]

go the other way
[CODE]
category      parentCategory
item            root
rental          root
event          root
couches item  
beds item
desks item
refrigerator item
tickets event
concerts event
clubs event
sports event
parties event
charity events  event
sublets rental
parking rental
[/CODE]

and suppose couches had two sub categories....making a 'sub-sub'
just had them as

category - parent
leather    - couches
fabric      - couches

there's no levels it's just referencing

basketball - sports
birthday - parties
21st birthdays - birthday
and so on

[fenway]

You know, if you're just going to come up with your own solution anyway, why ask for the experience of others?  Don't get me wrong, if you think your innovative solution will work, then go with it, but trust me, it's completely upside-down and entirely inflexible.