Organising my db's/tables

Hi all,

Once again I humbly seek the help of those with more clue than I ;)

I have agreed to take on a project for the charity I do some voluntary work for, and I'm already beginning to regret it!

The first thing I need help on is regarding the db structure, and try as I might, I cannot get my head round the best db layout for this job, so any advice is appreciated

Basically, the site (a directory of addresses, cross referenced) is structured like so:

Directory Listing of addresses
>Area
>>Category
>>Advert

So for example:

Listing:
>Area 1
>>Category 1
>>>Listing 1 (address & link to advert)
>>>Listing 2 (address & link to advert)
>>>Listing 3 (address & link to advert)

...etc etc with areas, categories, addresses and adverts.

The problem I have, is how to structure the db in an efficient way so that I can develop a system where other people can login to an admin section and administer the listing, as well as a public facing system that displays the listings in correct order.

The thing is, often a listing (we'll use abc charity for example) needs to be cross-referenced. So a user wishes to add abc charity to:

Area 1 under category type 3
AND
Area 1 under category type 6
AND
Area 3 under category type 3
AND display an advert, which is linked to from the listing.

How do I ensure that when a listing is added once, it can be listed under multiple areas/categories, and when it is deleted, all entries of this listing under all areas/categories are removed in one foul swoop?

I'm not asking how to do all this in PHP - that comes later. I just need to know how to arrange the db for this to work, so that I can efficiently administer and display the DB in PHP without ending up with a mess 6 months down the line.

If it helps, there are 20 fixed areas each with 30 fixed categories (same cats throughout) - these will never change.

Do I need to create tables for area, category & advert? If so, how do I join them so I can cross-SELECT & cross-INSERT/UPDATE them without vast amounts of PHP?

Does any of this make sense?!

TIA

Jonathen

http://www.wrox.com/books/1861006926.htm

have a table for your categories which have a id and refersto field so you can use the same table for subcategories (area, category and advert can all go in here)..

Then a advert table for the actual adverts, don't have a refersto field in here but give each advert an id.

then a advertlink table that contains a list of categoryid's and advertid's.. thereby linking them.. you'll have to figure the rest out by yourself though, coz it gets quite complex, especially when you want to start fuddling with permissions..