Re: Simple Database Design Problem

[Filip Nedyalkov]

Hi, to clarify - along with the table structure I tried to explain the site's folder structure and file structure, so you could understand what is a template (swf) or a component (grouping table for requests one to many) or a request (xml/php file). I guess since the component is defined by it's requests files, that means that two different components can't have the same request/s, meaning that the file_urls for the requests are unique for example:

component_1/php/request1.php - component_1 request
component_1/php/request2.php - component_1 request
component_2/php request1.php - component_2 request

So I guess we could change:
this-> UNIQUE(file_url)
to this-> UNIQUE(component_id,component_request_name,file_url)

Table description:
1) one theme -> many theme_templates
2) one theme_template -> many theme_page_templates
3) one theme_page_template <-> one template
4) one page_template <-> one template
5) many templates <-> many components (Templates_Components_Mapping table)
6) one component -> many requests

With some corrections mainly on the UNIQUE fields here is the table structure again as described above:

CREATE TABLE Themes (
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,
theme_id,
etc (lang_id),
PRIMARY KEY(theme_template_id),
UNIQUE(module_url,theme_id)
);

CREATE TABLE Theme_Page_Templates (
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),
UNIQUE(theme_template_id, theme_page_template_name)
);

CREATE TABLE Page_Templates (
page_template_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
template_id,
module_dir,
module_name,
module_url,
theme_id,
etc (module_preload_mode_name, module_preload_type_is_memory,lang_id)
PRIMARY KEY(page_template_id),
UNIQUE(module_url,theme_id)
);

CREATE TABLE Templates (
template_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
template_title,
template_desc,
PRIMARY KEY(template_id)
);

CREATE TABLE Components (
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(component_id,component_request_name,file_url)
);

CREATE TABLE Templates_Components_Mapping (
template_id,
component_id,
PRIMARY KEY(template_id,component_id)
);

Does this look good ? Anything that you consider a mistake ?