[Resolved] MySQL Database Design

jimwoolfe

please can anyone help a newbie
I want to rebuild my web site into a dynamic site using php and MySQL and I will be using Dreamweaver MX 2004 for most of the work. I've spent alot of time studying PHP, tried out abit of code and feel o.k.about this side of things.
Whats screwing with my mind is database design.
The site will have 4 front page sections with 6 first level sections containing a maximum of 7 sub sections each. I want to populate sections of the front page and sub sections via a database that will contain url links and page descriptions submited by our club members. Ive read up and sort of understand normalization but the only solution that I can come up to be able to format the data correctly to my site involves a table for each section (works out to 27 tables!!)
Now this seems wrong to me as every example of normalization I've seen has 1 table for each form.
I know that I'm missing something here. Any help, guildance will be very gratfully accepted

jim woolfe

superwormy

Post what you have so far...

jimwoolfe

Hi Superwormy

I have the following tables each representing a section that our members can post to / view

car modification, features,my plaything,shop,wheels,paintwork,body kits,window tinting, bove - spoilers,graphics,interior styling,suspension,brakes,engine,head units,proccessors,amps,speakers,bass boxes/boot installs,wiring.

each tables has the following fields

id url address description posted date

none of the sections cross reference on the site.
postings must be displayed in date order.
all postings will remain on the site unless space becomes a problem then I will remove oldest (can't see this happening for a long while).

hope this makes sense

jim w

mtimdog

Just add another field for section and 1 table for sections a.k.a.

id, name

1, "car mod"
2, "features"
....
....
....

Then link the tables by the fields section and id

jimwoolfe

cheers mtimdog

so I suppose this means I can have as many tables as I like. If so thats great as It means I,m on the right track

thanks

jim w

mtimdog

Sorry, I wasn't paying attention to your structure.

You should make the 2nd table look like

id, name, parent_id

where parent_id is 0 for top-level, or is the id of the top-level section. (this is for having 1 sub-level although you can make this as deep as you like assigning sections as parents to other sections).

jimwoolfe

Hi mtimdog

Right I am going to have a stab at this but before I start I just want to comfirm one thing

Am I limited to the amount of tables I can use ?

jim

mtimdog

No, but it would be easier to just have 2

1 for the (sub)sections and 1 for the posts

jimwoolfe

mtimdog

just wanted to thank you for your help. I've now designed and normalised my data base following your lead.

now comes the test !!!

thanks again

jim w