Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2008
    Posts
    14

    Question Unanswered: ERD and SQL script valid structure - very uncertian

    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`);

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2008
    Posts
    14
    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
    Attached Files Attached Files

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2008
    Posts
    14
    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
    Attached Files Attached Files
    Last edited by totus; 10-23-08 at 02:30.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2008
    Posts
    14
    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.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Oct 2008
    Posts
    14
    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`);
    Attached Files Attached Files

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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



    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Oct 2008
    Posts
    14
    Thanks r937 for all your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •