Firstly, I am new to SQL. I have taken a course and completed it successfully, but I seem to have trouble in designing my own data structures.I am attempting to create a database that stores domain, category, subcategory, and webpage information. My lack of experience gives much doubt to the structure I came with. There will be more than one domain (ex subdomains), multiple categories per domain, a few subcategories per category, and several webpages per subcategory. Currently, I am trying to use a primary key that uniquely identifies a group of fields in each table. For example, there may be two identically named subcategories that belong to different categories.I really am not sure, but it seems odd to eventually get to a table that references three foreign keys. I could find no other way to enforce group uniqueness though. I reviewed clustered unique indexes, but have found that there may only be one such index per table and that a primary key counts as that index. So the question is, how should I design my tables to enforce group uniqueness without requiring three foreign keys in the last table?My current structure:-- TABLE: DOMAIN--CREATE TABLE DOMAIN ( ID CHAR(30) PRIMARY KEY, TITLE CHAR(50) UNIQUE NOT NULL, KEYWORDS CHAR(80) NOT NULL, DESCRIPTION CHAR(255) NOT NULL);------ TABLE: DOMAIN_CATEGORY--CREATE TABLE DOMAIN_CATEGORY ( ID CHAR(30) NOT NULL, DOMAIN_ID CHAR(30) NOT NULL, TITLE CHAR(50) UNIQUE NOT NULL, KEYWORDS CHAR(80) NOT NULL, DESCRIPTION CHAR(255) NOT NULL, BANNER_ORDER INT, PRIMARY KEY (ID, DOMAIN_ID), FOREIGN KEY (DOMAIN_ID) REFERENCES DOMAIN (ID));------ TABLE: DOMAIN_SUBCATEGORY--CREATE TABLE DOMAIN_SUBCATEGORY ( ID CHAR(30) NOT NULL, DOMAIN_ID CHAR(30) NOT NULL, DOMAIN_CATEGORY_ID CHAR(30) NOT NULL, TITLE CHAR(50) UNIQUE NOT NULL, KEYWORDS CHAR(80) NOT NULL, DESCRIPTION CHAR(255) NOT NULL, PRIMARY KEY (ID, DOMAIN_ID, DOMAIN_CATEGORY_ID), FOREIGN KEY (DOMAIN_ID) REFERENCES DOMAIN (ID), FOREIGN KEY (DOMAIN_CATEGORY_ID) REFERENCES DOMAIN_CATEGORY (ID));------ Table: WEBPAGE--CREATE TABLE WEBPAGE ( ID CHAR(80) NOT NULL, DOMAIN_ID CHAR(30) NOT NULL, DOMAIN_CATEGORY_ID CHAR(30) NOT NULL, DOMAIN_SUBCATEGORY_ID CHAR(30) NOT NULL, TITLE CHAR(80) NOT NULL, KEYWORDS CHAR(80) NOT NULL, DESCRIPTION CHAR(255) NOT NULL, LOCATION CHAR(80), CONTENT MEDIUMTEXT, PRIMARY KEY (ID, DOMAIN_ID, DOMAIN_CATEGORY_ID, DOMAIN_SUBCATEGORY_ID), FOREIGN KEY (DOMAIN_ID) REFERENCES DOMAIN (ID), FOREIGN KEY (DOMAIN_CATEGORY_ID) REFERENCES DOMAIN_CATEGORY (ID), FOREIGN KEY (DOMAIN_SUBCATEGORY_ID) REFERENCES DOMAIN_SUBCATEGORY (ID));
Sheep Chops Cheap!!
I need foreign key help/advice
Posted - 2011-05-20 : 23:16:18
