creating tables

[piet bierbuik]

hi,

i want to create a site with the following link structuur.

[b]Headlink, Sublink, Name, Page[/b]

Like: Computers - Dell - Dell270 - p2,7 512 mb etc.
Computers - HP - HP120 - p3,7 1gig etc.
Games - Shooter - COD2 - new game blabla.

What do you guys think is the best table structuur for this site ?
what tables do i need to make and where to put the keys and things like that.

thx,

[obsidian]

if you're always going to have it 3 levels deep, i'd simply recommend having 3 optimized tables that reference the category directly above. what you call them is entirely up to you, but i'd do something like this:

[code]
TABLE 1 - LEVEL 1
=================
id | Name
---------------
1  | Computers
2  | Games


TABLE 2 - LEVEL 2 (references Table 1)
=================
id | ref | Name
---------------
1  |  1  | Dell
2  |  1  | HP
3  |  2  | Shooter


TABLE 3 - LEVEL 3 (references Table 2)
=================
id | ref |   Name   | Desc
-----------------------------
1  |  1  | Dell 270 | p2,7 512 mb etc.
2  |  2  | HP120    | p3,7 1gig etc.
3  |  3  | COD2     | new game blah, blah, blah
[/code]

then, you could very easily refer all the way back up the chain at any given time.

may not fit everything you have in mind, but with the examples you gave, it's one of the best ways i could think of off the top of my head.

hope this helps

[piet bierbuik]

[!--quoteo(post=346014:date=Feb 15 2006, 03:05 PM:name=obsidian)--][div class=\'quotetop\']QUOTE(obsidian @ Feb 15 2006, 03:05 PM) [snapback]346014[/snapback][/div][div class=\'quotemain\'][!--quotec--]
if you're always going to have it 3 levels deep, i'd simply recommend having 3 optimized tables that reference the category directly above. what you call them is entirely up to you, but i'd do something like this:

[code]
TABLE 1 - LEVEL 1
=================
id | Name
---------------
1  | Computers
2  | Games
TABLE 2 - LEVEL 2 (references Table 1)
=================
id | ref | Name
---------------
1  |  1  | Dell
2  |  1  | HP
3  |  2  | Shooter
TABLE 3 - LEVEL 3 (references Table 2)
=================
id | ref |   Name   | Desc
-----------------------------
1  |  1  | Dell 270 | p2,7 512 mb etc.
2  |  2  | HP120    | p3,7 1gig etc.
3  |  3  | COD2     | new game blah, blah, blah
[/code]

then, you could very easily refer all the way back up the chain at any given time.

may not fit everything you have in mind, but with the examples you gave, it's one of the best ways i could think of off the top of my head.

hope this helps
[/quote]

i think i get it, but why don't i make 1 table:

[code]
TABLE 1

id |ref|Name |Desc
-----------------------------
1  |  Computer  | Dell 270  | p2,7 512 mb etc.
2  |  Computer  | HP120 | p3,7 1gig etc.
3  |  Games | COD2 | new game blah, blah, blah
[/code]

[obsidian]

the only difference is the database optimization issue. with only one table, when you get to the point of having dozens, if not hundreds of records, you'll have a massive amount of duplicate data in your table. For instance, if you're reporting on 50 different games and 30 different PC setups, you'll have 80 "categories" listed where with pulling them out into their own table, you can have them listed only one time apiece.

the other reason i like to optimize as far as possible is in case you ever go to a CMS to control the pages. it's much easier to manage content when you have full control over the separate sections themselves as opposed to having to update EVERY record that contains the category "Computer".

either way will work, but always try to develop with the OPTION of future expansion when you can.

[piet bierbuik]

Ok, your right at the end its better to go your way.
thank you.
Ill start building the database right away.