How : Database Design ?

[Pirate]

I have couple of primitive ideas to do this job but I wanna see your suggestions guys . They might be much better than mine . I wanna design a database (Access XP) which holds items and subitems .For example:

Category One
-Item1
-Item2
-Item3
-Item4

Category Two
-Item1
-Item2
-Item3
-Item4

Category Three
-Item1
-Item2 (might include multiple subitems)
---Subitem2.1
---Subitem2.2
-Item3
-Item4

Category Four
-Item1
-Item2
-Item3
-Item4

Thanks in advance !

[Edneeis]

I'm not exactly sure what the question is but I'd lay it out like this:

Two (2) tables:

*Items (table)
ItemID (field) PK
Item (field)
Category (field)

*SubItems (table)
SubItemID (field) PK
ItemID (field) FK
SubItem (field)

PK=Primary Key of table
FK=Foreign Key - In this case links to Items.ItemID

[Pirate]

Thanks for the reply Ed but I don't think this approach will help me solving my problem since I would have a tree-like subsitems (maybe more than 2 to 6 subitems) . any other options .:rolleyes: ?

[Lethal]

Ed's data model defines a one to many approach, which is what you are describing unless I'm missing something.

[Edneeis]

Yes here is what the data would look like:

Items:
ItemID Item Category
1 Item1 1
2 Item2 1
3 Item3 1
4 Item4 1
5 Item1 2
6 Item2 2
7 Item3 2
8 Item4 2
9 Item1 3
10 Item2 3
11 Item3 3
12 Item4 3
13 Item1 4
14 Item2 4
15 Item3 4
16 Item4 4

SubItems:
SubItemID ItemID SubItem
1 6 SubItem2.1
2 6 SubItem2.2

[Edneeis]

You could actually do it with just one table but it may be problematic because you'd be referring to items in the same table in relationships. Also Top level items would have no parentID.

*Items (table)
ItemID (field) PK
Item (field)
Category (field)
ParentID (field)


Items:
ItemID Item Category ParentID
1 Item1 1
2 Item2 1
3 Item3 1
4 Item4 1
5 Item1 2
6 Item2 2
7 Item3 2
8 Item4 2
9 Item1 3
10 Item2 3
11 Item3 3
12 Item4 3
13 Item1 4
14 Item2 4
15 Item3 4
16 Item4 4
17 SubItem2.1 6
18 SubItem2.2 6

[Pirate]

Edneeis , you must be right now , but I'm too dumb to strcuture my data . Suppose we have these main Items :

Audio & Video
Players
Add-Ins
Encoders [includes: Wave , MP3 , DVD trees]

...etc

Internet
Download Managers [includes : FTP , HTTP]
Site Rippers
Chat
...etc

Games
Action
Arcade
...etc

Business
Database Management
...etc

I have more than this structure , ramified items . I'm really confused how to apply your approach here . What I did is as follow:
One table for (Main Categories) , One table (Subitems1 ,Subitems2 ,Subitems3) . This was really confusing . I will populate all these in multiple comboboxes . So I think (not sure) I should separate every category from the other .

Thanks :)

[Edneeis]

So then you got it working? Or not?

As a side note if the Category is not a number field you may want to put it in its own table. Especially if there is a chance that it has or will in the have in the future more properties then just a name. How does everyone else work that situation? Would you have Category as a seperate table or just a text field?

My thought in seperating it to a new table are:
-If it will someday have more than just a name
-To avoid spelling mistakes that would differentiate Categories
-To save space, since a number field instead of longer text field would be repeated

Of course it complicits the structure a little and may have a slight adjustment on performance having to query for the name.

[Pirate]

I got it working but I need to see if my implementation was ideal or not .

[Pirate]

I've had it this way ! Would this make it a professional-like ?

[Edneeis]

Sure you could do it that way although I'm not sure what structure you are using. This is what I was talking about, only with a third table for Categories.

[Edneeis]

Actually even better, here is the db. If you have Access XP or 2000 or whatever one has the expand/collapse bit so you can drill down the data, then data entry is easy and shows the structure well. If you want all the data together then you can use a query.

[Pirate]

Sorry for not replying your post Edneeis but I was busy installing my Satellite Connection . wow:D. anways , this seems the best solution but I have some questions : How did you make thos tables so they appear with "+" plus sign (like treeview) ? How simple is it to get the all specific category , for example internet ? the whole record down ?

Thank you !

[Edneeis]

The treeview bit is just part of Access, although you may have to have a relationship for it to do it, not sure. To get a specific category you just query for it either by its ID or some combination thereof. For instance this would get you all Items by CategoryID:

SELECT Items.Item
FROM Categories INNER JOIN Items ON Categories.CategoryID = Items.CategoryID
WHERE Categories.CategoryID)=?

Just Replace ? with the CategoryID (i.e. 2) or a parameter in a query.

[Pirate]

Sorry Edneeis but how can I view this relationships thingy ? I have XP Access ?:rolleyes:

[Edneeis]

Here is where it can be reached on the toolbar or you can build or edit relationships inside the query builder thing.

http://www.vbforums.com/attachment.php?s=&postid=1429433

[Pirate]

This makes it so pro-like . I think I will go for your way Edneeis . Sure I'll try this now , If I have any question , I'll pester you , :D . Thank you for being patient Ed .