Database Design Help

SiriusB

SiriusB

SiriusB

Soldato
Joined
16 Dec 2005
Posts
14,443
Location
Manchester
I am planning to build myself a personal website. Partly to teach myself PHP and MySQL along with exercising my current XHTML and CSS skils, and partly for my own vanity :p

Since I want my site to be database driven I need to create a good database before I think about doing anything else.

The way I am planning to set up my site is to have several sections that covers each of my main interests - Music, Computing, Hiking etc, and the content [or articles] for these parts of the site will be held in a database so i can add new stuff, edit stuff, make it searchable yadda yadda yadda.

Now, while I can design a very simple database, I am struggling with this one, simply because of the large amount of information I need to store.

Part of the problem is that one section [Hiking] requires fields describing the location of say a particular walk described in an article. I could add this information to the article body, but it would make automatically sorting the routes via different criteria [specific location, region, country etc] much harder. None of this info is needed for the other sections, which means a separate "Articles" table isn't going to cut it...

Below is an example of the data I would like to keep for my site:

Code: 
[FONT=Courier New]username  password  email         article_number article_title  article_body  article_date  article_section  article_subsection  article_area  article_region  article_country

siriusb   123       [email protected]  1              Llyn Ogwen     blah blah     28092007     hiking            route               Carneddau     Snowdonia       UK
bob11      321       [email protected]   2              New album      blaaaah       25092007     music             new                 NULL          NULL            NULL
jim99     111       [email protected]     3              V6 Client      bleh          28092007     computing         folding             NULL          NULL            NULL
[/FONT]
[Sorry about the rubbish formatting, don't know how to display tables nicely on here]

From the above, I came up with the following tables:

user_article
username [pk]
article_number [pk]

user
username [pk]
password
email

article
article_number [pk]
article_title
article_body
article_date
article_section
article_subsection
article_area
article_region
article_country

That's as far as I have gotten. I am not sure what to do from here. I know that section, subsection, area, region and country should be separated out into their own tables, to avoid redudancy... but I am not sure how the area/region/country table/s would interact with the articles table, since only specific articles [those belonging to Hiking] require that information...

As you can see it is all very complicated! Any help with this [or a different approach altogether] would be greatly appreciated.
 
One thing that you could do, and I have seen done with other databases, is to have an article_types table that specifies what type of article it is.

You would then have the basic articles table with all the information that is common to all articles and the article type.
You could then have a hiking_articles table and whateverelse_articles tables as needed, containing only the data relevant to that particular article.

This approach is OK if you're going to have only a few types of articles and they won't be changed regularly.

Another thing to try is to put an XML metadata column in the articles table to describe more fully the article.
Not sure if MySQL supports XML columns, but if it does that's something to think about.
 
A table with the same key is the same table, so even if some entries had some blank attributes I'd keep the same table so long as you're not going to have lots of hike-specific fields.

That means a little trickery if you want to determine what type each row is - but you could add a 'type' field in if you wanted, or do a query based on some logic such as "any row without a location isn't a hike" - but that's not really quite right.

One thing to note is the location country and region both depend on the area, ie they depend on an attribute which isn't the primary key of the table. So next step is to create another table or two:

article
...
location_area

location
area (pk)
region
country

You might want to add an ID in for the location in case you edit the location area's name, since MySQL isn't too good with referential integrity (that's sort-of true.)

Lastly, at the moment you can have multiple authors for each article - if that's not what you wanted, you can simplify by adding a 'user' field to the article table and remove the link table :)

The section/subsection is nice and easy - the simplest way is:

sections
id
parent
name

So then your article table looks like this:

article
article_number [pk]
article_title
article_body
article_date
article_section [which draws from the section table]
article_area
article_region
article_country

With some fun trickery you can then have unlimited subcategories too, and automatically create your menu etc.
 
OK, going over your post my database now looks like:

user
username [pk]
...
...

article

article_number [pk]
article_username [fk]
...
...
article_area [fk]
article_section [fk]
...
...

section
section_id [pk]
section_parent
section_name

area
area_id [pk]
area_name
area_region
area_country

The area table can probably be split again, but that shouldn't be too difficult.

The section table I am guessing would look something like this:

Code: 
[B]section_id section_parent section_name

[/B]1              Hiking                Routes
2              Music                New
3              NULL                 Computing
Or have I got the wrong end of the stick?

Concerning the location data, I don't think there will be that many articles needing the location data. Is it possible to have any articles that dont have location data connected to an empty field in the area table, or a record that says "N/A" for instance... or is it better to just leave the area foreign key field blank for those articles that don't require the area table?

Nearly all of it makes sense, it is just the little things I need to get clear in my mind. Thanks for your help so far :)
 
SiriusB said:
The area table can probably be split again, but that shouldn't be too difficult.
Click to expand...
Yup, it could - depends how complex you want the queries to get :)

The section table I am guessing would look something like this:

Code: 
[B]section_id section_parent section_name

[/B]1              Hiking                Routes
2              Music                New
3              NULL                 Computing
Or have I got the wrong end of the stick?
Click to expand...
Close - the parent would be an ID so that you can have sub-sections. Eg:

1 - 0 - Music [no parent - top-level option]
2 - 1 - Rock [sub-category of music]
3 - 0 - Hiking [category for hiking]
4 - 0 - 2006 [sub-category for hiking]

Concerning the location data, I don't think there will be that many articles needing the location data. Is it possible to have any articles that dont have location data connected to an empty field in the area table, or a record that says "N/A" for instance... or is it better to just leave the area foreign key field blank for those articles that don't require the area table?
Click to expand...
Bingo, you can leave the location as NULL (no value) :)
 
Beansprout said:
Yup, it could - depends how complex you want the queries to get :)
Click to expand...

Complicated SQL doesn't scare me! looks for nearest big stick

Close - the parent would be an ID so that you can have sub-sections. Eg:

1 - 0 - Music [no parent - top-level option]
2 - 1 - Rock [sub-category of music]
3 - 0 - Hiking [category for hiking]
4 - 0 - 2006 [sub-category for hiking]
Click to expand...

Ahh, I see. So if I wanted to list all subsections of say Music, I could have:
Code: 
SELECT * FROM section WHERE section_parent=1
Very clever :D

Bingo, you can leave the location as NULL (no value) :)
Click to expand...

Brilliant! Thanks again for your help. I knew I was on the right track, I just needed a nudge. Perhaps I should have finished the Database module before leaving uni :p
 
I'd suggest having a user_id. It takes up less room in the cookies and gives you more freedom for if the user wants to change their name.
 
SiriusB said:
Ahh, I see. So if I wanted to list all subsections of say Music, I could have:
Code: 
SELECT * FROM section WHERE section_parent=1
Very clever :D
Click to expand...
If you only have one sub-category, yes :D

If you're interested in learning more, deep withing my library of bookmarks I have this link which contains more than you'll ever want to know on the subject of hierarchical data, though you might've covered it at uni (but probably not in the context of MySQL) :)
 
Oof that's a read!

It does look interesting though - and nested sets are easier to get your head around too!

Just need to learn more SQL - especially JOINS - to fully understand that lot! Still, that's why my website is for, learning :)
 
You must log in or register to reply here.
Back
Top Bottom