I'm trying to build a new db for a website and I've got an idea on how it should work but I can't seem to figure out what the best way would be if it comes to the design of the tables in the db.
Maybe I'm just looking at the the wrong way...
I would like to make the website completely db driven but path based.
TO get rid of the ugly uri's like index.php?pad=blaat&naar=sjips&bestand=opti I would like to use mod_rewrite so the same page can be accessed using www.domain.nl/blaat/sjips/opti. This part isn't realy a problem.
The problem is how should this be implemented in the db?
In the db the different levels have to be known so "sjips" is a subcategory of "blaat" and "opti" is a subcategory of "sjips" agian.
In the db the name of each category and page has to be stored.
This has the be easily extendable because at first there might not be a subcategory of a subcategory. Imo there shouldn't be a different column or table for each category to list it's subs, am I right?
What I could be is created a table with e.g. 3 columns, one containing an unique id for each category and page, one containing each unique name of that category of page and one column containing the id of its parent.
I'm not sure if this would be a good thing to do from a coding point of view because you have to contact the db multiple times to get all the information about on specific category or page, wouldn't you?
It look like a simple concept imo but it might be the lack of sleep not getting me think strait anymore...
Am I overlooking something?
Am I creating problems which aren't there?
The problem is, I haven't got a lot of experience designing a db structure. The stuff I did always needed to be altered significantly during the process of building a website or extending it later on. Although I realize alterations will away be needed during the development, I would like to try to minimizing this. Because of this I would like to hear what your view is on a db structured the way I tried to illustrate.
It will be used for a website which doesn't need any complex stuff included. It will be multilingual, first only dutch and english and later on one or two additional languages.
Language dependent information that needs to be stored is category names, category descriptions, page names, page titles and page contents.
The menu items are going to be db generated which is a reason I want to make sure the structure illustrated in the first post is thought over well.
The main structure is going to be an html template so the page content will only contain a minimum of html tags (h1, br, etc.) and the content will be "static" stored in the db.
I started creating a different tables for each language which makes it easily extendable but has the disadvantage you have to create a new table again each time you want to add a language.
Also, not all categories will be available in each language.