Re: Simple Database Design Problem
Thanks for the great reply and sorry for the confusion. I devided my database design problem into 2 examples to make it simplier.
Ok so about the books,authors example you nailed it. I just can't figure out how to: "get away from the external data (author name) to the the internal id".
And about the templates question it's like persons,actors,directors example.
I will try to put these 2 examples all together now going back to my database design with the original tables and fields I have (those in brackets after etc are not important at this point).
First I will explain how things work more detailed and then I will put it all in a several table structure which you could advise me how to correct/adjust to make it all work... Here we go:
Within the application the user can create themes by specifying a name and install templates and components for the theme to use. A word about how are my system files structured and how it all works:
1) SUMMARY - in flash I can put components in swf files. Saying that, if I want to build a modular application, I need to put some components in one swf, other in another etc. So the site structure is simple: You load one theme module (swf file) that loads a page module for the requested page (swf file). If you want to change the design of everything or switch some components in the theme module or page module you need to make a different swf for that purpose. That's why the theme can have many theme_templates (swfs) and the pages can have many page_templates (swfs). The theme_page_template is a page that is not in a separate page module but component inside the theme_template and has a unique name so it can be identified and loaded when the page is requested. Another important thing to mention here is: The reason there are components separate from templates is that this way the templates will act as containers for components while the components are related to the data via xml/php file requests. With this you can make any changes to the templates component structure without loosing any data.
2) THEME - a folder with all the project files, including subfolders: page_templates and components, and a start file to run the application from.
3) THEME_TEMPLATE - this is the swf file for the theme to load. It has all theme_page_templates defined.
4) THEME_PAGE_TEMPLATE - a component inside of the theme_template (swf) that acts as a page container with components.
5) PAGE_TEMPLATE - the page_templates is a swf (flash file) which are located under the page_templates folder. It could be in a subfolder of that folder etc. Since the swf file name must be unique in the folder it is in, this could be the unique identifier for it.
6) COMPONENTS are not separate files, they can be placed in a page_template/theme_page_template which acts as a container for them, BUT they do have xml/php files they use to fetch the data for them, which are located under the components folder. The request files could be in a subfolder of that folder etc. Since the request xml/php files names must be unique in the folder they are in, they could be the unique identifier for the component.
So here is my problem table structure at the moment...:
CREATE TABLE Themes (Book Library) (
theme_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
theme_title,
PRIMARY KEY(theme_id),
);
CREATE TABLE Theme_Template (
theme_template_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
template_title,
template_desc,
module_dir,
module_name,
module_url,
etc (theme_id,lang_id),
PRIMARY KEY(theme_template_id),
UNIQUE(module_url)
);
Note: not sure if making the module_url unique is correct but seems like it. module_dir is the directory + module_name is the swf name = module_url
CREATE TABLE Theme_Page_Templates (Directors) (
theme_page_template_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
template_id,
theme_template_id,
theme_page_template_name,
PRIMARY KEY(theme_page_template_id)
);
Note: theme_page_template_name is a unique name for the page template but only within a particular theme_template.
CREATE TABLE Page_Templates (Actors) (
page_template_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
template_id,
module_dir,
module_name,
module_url,
etc (module_preload_mode_name, module_preload_type_is_memory,theme_id,lang_id)
PRIMARY KEY(page_template_id),
UNIQUE(module_url)
);
Note: not sure if making the module_url unique is correct but seems like it. module_dir is the directory + module_name is the swf name = module_url
CREATE TABLE Templates (Books/Persons) (
template_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
template_title,
template_desc,
PRIMARY KEY(template_id)
);
CREATE TABLE Components (Authors) (
component_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
component_title,
component_desc,
theme_id,
etc (lang_id),
PRIMARY KEY(component_id)
);
CREATE TABLE Components_Requests (
component_request_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
component_id,
component_request_name,
request_title,
file_dir,
file_name,
file_url,
PRIMARY KEY(component_request_id),
UNIQUE(file_url)
);
Note: not sure if making the file_url unique is correct but seems like it. file_dir is the directory + file_name is the xml/php file name = file_url
CREATE TABLE Templates_Components_Mapping (
template_id,
component_id,
PRIMARY KEY(template_id,component_id)
);
So at this point I don't seem to be able create the logic between Tables: Templates, Components, Components_Requests properly.
It seems logical to have 1 folder for components and 1 for page_templates having in mind that any page template can use any component.
If I package my templates and my components and give them to you, and by accident some of my components match some of your components folder and request files names.. you would have to change the folder names for example of my components which in turn changes where the script, I've wrote to install my components in the system, would have to look for them. Not sure if that is a big problem.. seems managable.
On the other hand if you package the components a template uses inside it's folder, even if you change the folder name where the template is, the script would just take the current for it and won't have trouble finding the components cause their structure is intact. But then wouldn't it seem strange if you make some new templates in new folders and use some components which are located in another template's folder. It doesn't make any sense, especially if you decide to delete a template folder.. that would delete the components that originally came with that template (the request files) but you would still use them with other templates...
Would be great if you could tell me what you think of all this. Thank you a lot!