ERD and SQL script valid structure - very uncertian

totus · #1 (**permalink**) 10-21-08, 23:52

Its been about 15 years since I've done any ERDs and am working on a little pet project here. I feel like I'm close but would really appreciate a second pair of eyes to see if theres a better way than how I've structured this ERD.

This is my first stab at setting up tag tables. From what I read the way I set up the tag tables is the toxi solution.

Any help from you gurus is greatly appreciated.

I hope the comments are explanatory enough.

Code:

/* SQLEditor (MySQL)*/


CREATE TABLE `SubCategory`
(
`SubCategoryID` INTEGER UNIQUE ,
`SubCategoryName` VARCHAR
);


/*SiteAdvertising Comment
This table will list the types of advertising each social site offers. e.g. PPC, CPM*/


CREATE TABLE `Categories`
(
`CategoryID` INTEGER UNIQUE ,
`CategoryName` VARCHAR,
`SubCategoryID` INTEGER
);



CREATE TABLE `SEOGrade`
(
`SEOGradeID` INTEGER UNIQUE ,
`SEOGrade` CHAR
);



CREATE TABLE `RegionalTargets`
(
`RegionalID` INTEGER UNIQUE ,
`Region` CHAR
);



CREATE TABLE `Tags`
(
`TagID` INTEGER unsigned  NOT NULL AUTO_INCREMENT ,
`TagText` CHAR NOT NULL UNIQUE ,
PRIMARY KEY (`TagID`)
);



CREATE TABLE `AgeTargets`
(
`AgeID` INTEGER UNIQUE ,
`AgeRanges` INTEGER
);



CREATE TABLE `TagMap`
(
`SiteID` INTEGER unsigned  NOT NULL,
`TagMapID` INTEGER unsigned  NOT NULL,
`TagID` INTEGER,
PRIMARY KEY (`SiteID`,`TagMapID`)
);



CREATE TABLE `Social_Sites`
(
`SiteID` INTEGER unsigned  NOT NULL AUTO_INCREMENT ,
`SiteName` VARCHAR(150) UNIQUE ,
`AlexaRank` INTEGER(100000000000),
`PageRank` INTEGER(10),
`ShortDescription` VARCHAR(300),
`LongDescription` VARCHAR(1000),
`RegisteredUsers` INTEGER,
`InBoundLinks` INTEGER,
`OutBoundLinks` INTEGER,
`LinksValid` INTEGER,
`DomainAge` DATE,
`AgeID` INTEGER,
`RegionalID` INTEGER,
`SEOGradeID` INTEGER,
`ContentSupport` INTEGER,
`BusinessBenefit` VARCHAR,
`CategoryID` INTEGER,
`SubCategoryID` INTEGER,
`SiteAdvertisingID` INTEGER,
PRIMARY KEY (`SiteID`)
);


/*Categories Comment
Categories will be brocken down into two levels.  1st level and Second level.  Each Social Site can be in more than one second level category.*/

/*AgeRanges Comment
Age ranges will be statis e.g. 18-25, 18-35, 25-45, etc.. etc...*/

/*RegionalTargets Comment
This table will contain a list of every country name and the word global.  Each social site can be in more than one country.*/

/*SEOGrade Comment
Each Social Site will be Graded by 5 different users from an SEO persective.  Grade will range from A - F*/


CREATE TABLE `SiteAdvertising`
(
`SiteAdvertisingID` INTEGER UNIQUE ,
`TypeOfAdvertising` CHAR
);


ALTER TABLE `Categories` ADD FOREIGN KEY (`SubCategoryID`) REFERENCES `SubCategory`(`SubCategoryID`);
CREATE INDEX `Tags_TagText_idx`  ON `Tags`(`TagText`);
CREATE INDEX `TagMap_SiteID_idxfk`  ON `TagMap`(`SiteID`);
ALTER TABLE `TagMap` ADD FOREIGN KEY (`SiteID`) REFERENCES `Social_Sites`(`SiteID`);
CREATE INDEX `TagMap_TagMapID_idx`  ON `TagMap`(`TagMapID`);
ALTER TABLE `TagMap` ADD FOREIGN KEY (`TagID`) REFERENCES `Tags`(`TagID`);
CREATE INDEX `Social_Sites_AgeID_idx`  ON `Social_Sites`(`AgeID`);
ALTER TABLE `Social_Sites` ADD FOREIGN KEY (`AgeID`) REFERENCES `AgeTargets`(`AgeID`);
CREATE INDEX `Social_Sites_RegionalID_idx`  ON `Social_Sites`(`RegionalID`);
ALTER TABLE `Social_Sites` ADD FOREIGN KEY (`RegionalID`) REFERENCES `RegionalTargets`(`RegionalID`);
CREATE INDEX `Social_Sites_SEOGradeID_idx`  ON `Social_Sites`(`SEOGradeID`);
ALTER TABLE `Social_Sites` ADD FOREIGN KEY (`SEOGradeID`) REFERENCES `SEOGrade`(`SEOGradeID`);
ALTER TABLE `Social_Sites` ADD FOREIGN KEY (`CategoryID`) REFERENCES `Categories`(`CategoryID`);
ALTER TABLE `Social_Sites` ADD FOREIGN KEY (`SubCategoryID`) REFERENCES `SubCategory`(`SubCategoryID`);
ALTER TABLE `Social_Sites` ADD FOREIGN KEY (`SiteAdvertisingID`) REFERENCES `SiteAdvertising`(`SiteAdvertisingID`);

r937 · #2 (**permalink**) 10-22-08, 04:54

i see a small problem with your tags table -- tagtext is CHAR

it should be VARCHAR, and it should have a length

whether it needs the auto_increment is very much debatable -- i prefer not

also, your tagmap table definitely does not need the tagmapid column

have a look here -- http://forge.mysql.com/wiki/TagSchema

totus · #3 (**permalink**) 10-22-08, 18:21

Quote:

Originally Posted by r937

i see a small problem with your tags table -- tagtext is CHAR

it should be VARCHAR, and it should have a length

whether it needs the auto_increment is very much debatable -- i prefer not

also, your tagmap table definitely does not need the tagmapid column

have a look here -- http://forge.mysql.com/wiki/TagSchema

Thanks so much r937. I wasn't sure about that. I've attached the actual ERD in pdf format. I'm not sure about the category tables, are these right? Thanks so much for your time and knowledge.

totus

r937 · #4 (**permalink**) 10-22-08, 20:19

i don't understand your category/subcategory tables (the cat links to the subcat? that's weird)

i think you only need one table for bofadem¹

have a look here: Categories and Subcategories

¹ i can never pass up an opportunity to use one of my favourite words, "bofadem"

totus · #5 (**permalink**) 10-23-08, 00:33

Quote:

Originally Posted by r937

i don't understand your category/subcategory tables (the cat links to the subcat? that's weird)

i think you only need one table for bofadem¹

have a look here: Categories and Subcategories

¹ i can never pass up an opportunity to use one of my favourite words, "bofadem"

I've made some revisions to the categories table and added a few other tables. Almost there! Thank you so much, can I give you a starbucks paypal donation? I've uploaded the new ERD for review.

I've attempted an to create the tables and I'm getting the following errors:

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
`CategoryID` INTEGER,
`SiteAdvertisingID` INTEGER,
`KeywordDensityID` INTEGE' at line 19

Code:

/* SQLEditor (MySQL)*/

DROP TABLE IF EXISTS `TagMap`;

DROP TABLE IF EXISTS `Social_Sites`;

DROP TABLE IF EXISTS `AgeRanges`;

DROP TABLE IF EXISTS `SiteAdvertising`;

DROP TABLE IF EXISTS `Tags`;

DROP TABLE IF EXISTS `RegionalPopularity`;

DROP TABLE IF EXISTS `Categories`;

DROP TABLE IF EXISTS `PresenceGrade`;

DROP TABLE IF EXISTS `KeywordDensity`;


CREATE TABLE `AgeRanges`
(
`AgeID` INTEGER UNIQUE ,
`AgeRanges` INTEGER
);



CREATE TABLE `SiteAdvertising`
(
`SiteAdvertisingID` INTEGER UNIQUE ,
`TypeOfAdvertising` CHAR
);



CREATE TABLE `Tags`
(
`TagID` INTEGER unsigned  NOT NULL,
`TagText` VARCHAR(25) NOT NULL UNIQUE ,
PRIMARY KEY (`TagID`)
);



CREATE TABLE `TagMap`
(
`SiteID` INTEGER unsigned  NOT NULL,
`TagID` INTEGER,
PRIMARY KEY (`SiteID`)
);


/*PresenceGrade Comment
Each Social Site will be Graded by 5 different users from an SEO persective.  Grade will range from A - F and will be determined by evaulated data within other tables.*/


CREATE TABLE `Social_Sites`
(
`SiteID` INTEGER unsigned  NOT NULL AUTO_INCREMENT ,
`SiteName` VARCHAR(150) UNIQUE ,
`AlexaRank` INTEGER,
`PageRank` INTEGER(10),
`PageIndexCount` INTEGER,
`AgeID` INTEGER,
`ShortDescription` VARCHAR(300),
`LongDescription` VARCHAR(1000),
`RegisteredUsers` INTEGER,
`InBoundLinks` INTEGER,
`OutBoundLinks` INTEGER,
`LinksValid` INTEGER,
`DomainAge` DATE,
`SEOGradeID` INTEGER,
`RegionalID` INTEGER,
`ContentSupport` INTEGER,
`BusinessBenefit` VARCHAR,
`CategoryID` INTEGER,
`SiteAdvertisingID` INTEGER,
`KeywordDensityID` INTEGER,
`ClassC` INTEGER,
PRIMARY KEY (`SiteID`)
);


/*SiteAdvertising Comment
This table will list they types of advertising each social site offers. e.g. PPC, CPM*/


CREATE TABLE `RegionalPopularity`
(
`RegionalID` INTEGER UNIQUE ,
`Region` CHAR
);


/*RegionalPopularity Comment
This table will contain a list of every country name and the word global.  Each social site can be in more than one country.*/

/*Categories Comment
Categories will be broken down into two levels.  Top and Bottom.  Each Social Site can be in more than one static category and/or sub category.*/


CREATE TABLE `Categories`
(
`CategoryID` INTEGER NOT NULL,
`CategoryName` VARCHAR(70) NOT NULL,
`ParentID` INTEGER,
PRIMARY KEY (`CategoryID`)
);



CREATE TABLE `PresenceGrade`
(
`SEOGradeID` INTEGER UNIQUE ,
`SEOGrade` CHAR
);


/*AgeRanges Comment
Age ranges will be specified as e.g.

18-25
25-35
36-50
etc..
etc..*/


CREATE TABLE `KeywordDensity`
(
`KeywordDensityID` INTEGER UNIQUE ,
`Keyword` VARCHAR(50),
`DensityPercent` INTEGER(100)
);


CREATE INDEX `Tags_TagText_idx`  ON `Tags`(`TagText`);
CREATE INDEX `TagMap_SiteID_idxfk`  ON `TagMap`(`SiteID`);
ALTER TABLE `TagMap` ADD FOREIGN KEY (`SiteID`) REFERENCES `Social_Sites`(`SiteID`);
ALTER TABLE `TagMap` ADD FOREIGN KEY (`TagID`) REFERENCES `Tags`(`TagID`);
CREATE INDEX `Social_Sites_AgeID_idx`  ON `Social_Sites`(`AgeID`);
ALTER TABLE `Social_Sites` ADD FOREIGN KEY (`AgeID`) REFERENCES `AgeRanges`(`AgeID`);
CREATE INDEX `Social_Sites_SEOGradeID_idx`  ON `Social_Sites`(`SEOGradeID`);
ALTER TABLE `Social_Sites` ADD FOREIGN KEY (`SEOGradeID`) REFERENCES `PresenceGrade`(`SEOGradeID`);
CREATE INDEX `Social_Sites_RegionalID_idx`  ON `Social_Sites`(`RegionalID`);
ALTER TABLE `Social_Sites` ADD FOREIGN KEY (`RegionalID`) REFERENCES `RegionalPopularity`(`RegionalID`);
ALTER TABLE `Social_Sites` ADD FOREIGN KEY (`CategoryID`) REFERENCES `Categories`(`CategoryID`);
ALTER TABLE `Social_Sites` ADD FOREIGN KEY (`SiteAdvertisingID`) REFERENCES `SiteAdvertising`(`SiteAdvertisingID`);
ALTER TABLE `Social_Sites` ADD FOREIGN KEY (`KeywordDensityID`) REFERENCES `KeywordDensity`(`KeywordDensityID`);
ALTER TABLE `Categories` ADD FOREIGN KEY (`ParentID`) REFERENCES `Categories`(`CategoryID`);

Cheers,
totus

r937 · #6 (**permalink**) 10-23-08, 06:42

the donation is not necessary

instead, i would ask you to pay it forward and help others in turn

your error message clearly points to where the error occurred -- just before CategoryID you have BusinessBenefit VARCHAR, and VARCHAR always requires a length

also, your TagMap table has SiteID as the PK, which means that each site can have at most one tag

here's an alternate suggestion

replace the 2 tables Tags and Tagmap with the following:

CREATE TABLE SiteTags
( SiteID INTEGER unsigned NOT NULL
, CONSTRAINT SiteID_fk FOREIGN KEY (SiteID) REFERENCES Social_Sites (SiteID)
, Tag VARCHAR(25) NOT NULL
, PRIMARY KEY (SiteID,Tag)
);

no Tags table means that you will be able to add as many different tags to a site as you want, without "pre-registering" each tag in the Tags table

if, however, you want to pre-register all your tags, then add the Tags table as follows --

CREATE TABLE Tags
( Tag VARCHAR(25) NOT NULL PRIMARY KEY
);

and then add the FK to the SiteTags table afterwards

totus · #7 (**permalink**) 10-23-08, 16:37

Quote:

Originally Posted by r937

the donation is not necessary

instead, i would ask you to pay it forward and help others in turn

your error message clearly points to where the error occurred -- just before CategoryID you have BusinessBenefit VARCHAR, and VARCHAR always requires a length

also, your TagMap table has SiteID as the PK, which means that each site can have at most one tag

here's an alternate suggestion

replace the 2 tables Tags and Tagmap with the following:

CREATE TABLE SiteTags
( SiteID INTEGER unsigned NOT NULL
, CONSTRAINT SiteID_fk FOREIGN KEY (SiteID) REFERENCES Social_Sites (SiteID)
, Tag VARCHAR(25) NOT NULL
, PRIMARY KEY (SiteID,Tag)
);

no Tags table means that you will be able to add as many different tags to a site as you want, without "pre-registering" each tag in the Tags table

if, however, you want to pre-register all your tags, then add the Tags table as follows --

CREATE TABLE Tags
( Tag VARCHAR(25) NOT NULL PRIMARY KEY
);

and then add the FK to the SiteTags table afterwards

r937 I did what you suggested in reference to creating one table called SiteTags. However setting two Primiary Keys for both SiteID and Tag I get the following error when creating the tables.

Error

SQL query:

ALTER TABLE `Social_Sites` ADD FOREIGN KEY ( `SiteID,Tag` ) REFERENCES `SiteTags` ( `SiteID,Tag` ) ;

MySQL said: Documentation
#1072 - Key column 'SiteID,Tag' doesn't exist in table

Do I have to create a foreign key to the new TagID in the Social_Sites table?

The reason why I had two tables is because of this reference which stated that the toxi or scuttle method was best. http://forge.mysql.com/wiki/TagSchem...d_Architecture I'm needing the most robust tagging method possible. i want to search via word counts, word associations, compare data from other tables to words counts, etc...

Thanks again for so much attention! I can't tell you how much you've helped me out already, some of this if finally coming back to me.

r937 · #8 (**permalink**) 10-23-08, 16:39

stop using those silly backticks, and the error goes away

`SiteID,Tag` is one column name, with a comma inside the name (and this column does not, of course, exist)

SiteID,Tag (without backticks) is two columns

r937 · #9 (**permalink**) 10-23-08, 16:41

oh, and by the way, the Social_Sites table should not have a FK to SiteTags

SiteTags needs one FK to Social_Sites, and another FK to Tags, assuming you do want to have the Tags tabls

totus · #10 (**permalink**) 10-23-08, 16:48

Okay, final ERD and Script...

attached.

Code:

/* SQLEditor (MySQL)*/

DROP TABLE IF EXISTS `SiteTags`;

DROP TABLE IF EXISTS `SocialSites`;

DROP TABLE IF EXISTS `AgeRanges`;

DROP TABLE IF EXISTS `SiteAdvertising`;

DROP TABLE IF EXISTS `RegionalPopularity`;

DROP TABLE IF EXISTS `Categories`;

DROP TABLE IF EXISTS `PresenceGrade`;

DROP TABLE IF EXISTS `KeywordDensity`;


CREATE TABLE `AgeRanges`
(
`AgeID` INTEGER UNIQUE ,
`AgeRanges` INTEGER
);



CREATE TABLE `SiteAdvertising`
(
`SiteAdvertisingID` INTEGER UNIQUE ,
`TypeOfAdvertising` CHAR
);


/*PresenceGrade Comment
Each Social Site will be Graded from A - F and will be determined by evaulated data within other tables.*/


CREATE TABLE `SocialSites`
(
`SiteID` INTEGER unsigned  NOT NULL AUTO_INCREMENT ,
`SiteName` VARCHAR(150) UNIQUE ,
`AlexaRank` INTEGER,
`PageRank` INTEGER(10),
`PageIndexCount` INTEGER,
`ShortDescription` VARCHAR(300),
`LongDescription` VARCHAR(1000),
`RegionalID` INTEGER,
`RegisteredUsers` INTEGER,
`InBoundLinks` INTEGER,
`OutBoundLinks` INTEGER,
`LinksValid` INTEGER,
`DomainAge` DATE,
`SEOGradeID` INTEGER,
`AgeID` INTEGER,
`ContentSupport` INTEGER,
`BusinessBenefit` VARCHAR(500),
`CategoryID` INTEGER,
`SiteAdvertisingID` INTEGER,
`KeywordDensityID` INTEGER,
`ClassC` INTEGER,
`Tag` VARCHAR(25),
PRIMARY KEY (`SiteID`)
);


/*SiteAdvertising Comment
This table will list they types of advertising each social site offers. e.g. PPC, CPM*/


CREATE TABLE `RegionalPopularity`
(
`RegionalID` INTEGER UNIQUE ,
`Region` CHAR
);


/*RegionalPopularity Comment
This table will contain a list of every country name and the word global.  Each social site can be in more than one country.*/

/*Categories Comment
Categories will be broken down into two levels.  Top and Bottom.  Each Social Site can be in more than one static category and/or sub category.*/


CREATE TABLE `Categories`
(
`CategoryID` INTEGER NOT NULL,
`CategoryName` VARCHAR(70) NOT NULL,
`ParentID` INTEGER,
PRIMARY KEY (`CategoryID`)
);



CREATE TABLE `PresenceGrade`
(
`SEOGradeID` INTEGER UNIQUE ,
`SEOGrade` CHAR
);


/*AgeRanges Comment
Age ranges will be specified as e.g.

18-25
25-35
36-50
etc..
etc..*/


CREATE TABLE `KeywordDensity`
(
`KeywordDensityID` INTEGER UNIQUE ,
`Keyword` VARCHAR(50),
`DensityPercent` INTEGER(100)
);



CREATE TABLE `SiteTags`
(
`SiteID` INTEGER unsigned  NOT NULL,
`Tag` VARCHAR(25) NOT NULL,
PRIMARY KEY (`SiteID`,`Tag`)
);


CREATE INDEX `SocialSites_RegionalID_idx`  ON `SocialSites`(`RegionalID`);
ALTER TABLE `SocialSites` ADD FOREIGN KEY (`RegionalID`) REFERENCES `RegionalPopularity`(`RegionalID`);
CREATE INDEX `SocialSites_SEOGradeID_idx`  ON `SocialSites`(`SEOGradeID`);
ALTER TABLE `SocialSites` ADD FOREIGN KEY (`SEOGradeID`) REFERENCES `PresenceGrade`(`SEOGradeID`);
CREATE INDEX `SocialSites_AgeID_idx`  ON `SocialSites`(`AgeID`);
ALTER TABLE `SocialSites` ADD FOREIGN KEY (`AgeID`) REFERENCES `AgeRanges`(`AgeID`);
ALTER TABLE `SocialSites` ADD FOREIGN KEY (`CategoryID`) REFERENCES `Categories`(`CategoryID`);
ALTER TABLE `SocialSites` ADD FOREIGN KEY (`SiteAdvertisingID`) REFERENCES `SiteAdvertising`(`SiteAdvertisingID`);
ALTER TABLE `SocialSites` ADD FOREIGN KEY (`KeywordDensityID`) REFERENCES `KeywordDensity`(`KeywordDensityID`);
ALTER TABLE `Categories` ADD FOREIGN KEY (`ParentID`) REFERENCES `Categories`(`CategoryID`);
CREATE INDEX `SiteTags_SiteID_idxfk`  ON `SiteTags`(`SiteID`);
ALTER TABLE `SiteTags` ADD FOREIGN KEY (`SiteID`) REFERENCES `SocialSites`(`SiteID`);

r937 · #11 (**permalink**) 10-23-08, 18:53

my friend, this is where you learn to fly

i am not going to do your testing for you

there are a number of problems i see, and i will give you a hint for two of them

if i am not mistaken, CHAR by itself will either default to CHAR(1) or give a syntax error

and your age ranges like "18-25" will not fit into an INTEGER column

the rest, i think you can find

good luck

totus · #12 (**permalink**) 10-23-08, 19:17

Thanks r937 for all your help.

Internet Services

Web Development

Digital Marketing

Consumer Tech