Database Design
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Database Design

  1. #1

    Database Design


    Let's pretend you have a CMS. This CMS allows has a blog (post, edit, delete) like method of managing files for others. You have a documents section, a downloads section, a picture section, an portfolio section, etc. All of these sections are sort of the same in the database. These are the columns:

    ID - A index number according to this row
    poster - Name of the person who posted
    title - Title of post
    the date - self explanatory
    message- details, a text section
    link - A link to the content, I.E. downloads section has a link to a file.

    Now, there is also a News section... This news section's posts do not have a title, or link, which means those columns will be unused.

    I feel like these are my choices:
    -I have 1 table with all of the sections that contain content (titles, links), and another table for the news?
    -I have 1 table with all of the sections, including news, and leave the link and title as null values in the news posts. I add a column to identify which section the row belongs to.
    -I have a table for each section. This is my current setup, which I don't like.

    Any ideas will be helpful. Thanks!

  2. #2
    Senior Member
    Join Date
    Nov 2001
    Posts
    1,255

    Re: Database Design

    Originally posted here by Soda_Popinsky
    I feel like these are my choices:
    -I have 1 table with all of the sections that contain content (titles, links), and another table for the news?
    -I have 1 table with all of the sections, including news, and leave the link and title as null values in the news posts. I add a column to identify which section the row belongs to.
    -I have a table for each section. This is my current setup, which I don't like.
    To be optimal, it would help to know what datatypes you are specifying each field as. From the sounds of it, initially the first option will give you the best DB size -> performance ratio, assuming you keep your indexes. In terms of raw performance and scalability, the table per section will be the best option, but that's really once you hit the multiply gigabytes of data stage.
    Chris Shepherd
    The Nelson-Shepherd cutoff: The point at which you realise someone is an idiot while trying to help them.
    \"Well as far as the spelling, I speak fluently both your native languages. Do you even can try spell mine ?\" -- Failed Insult
    Is your whole family retarded, or did they just catch it from you?

  3. #3
    I think this is my preferred setup:

    scms_links InnoDB
    scms_content InnoDB // This will contain news and content, although there will be 2 empty values in each news post
    scms_style InnoDB

    I haven't put much effort in designing the DB yet, so there are probably obvious problems with the setup. I am pretty new to MySQL and databases in general. Suggestions are encouraged, if there are things I'm ignoring that needs to be worked on, let me know.

    CREATE TABLE `scms_content` (
    `ID` int(11) NOT NULL auto_increment,
    `Poster` varchar(30) default NULL,
    `Title` varchar(100) default NULL,
    `TheDate` varchar(5) default NULL,
    `Message` text,
    `Link` varchar(100) NOT NULL default '',
    PRIMARY KEY (`ID`)
    ) TYPE=InnoDB AUTO_INCREMENT=2 ;

    I don't think I will be designing this to handle gig's of data, but I do want it to be capable of doing so. I think I will be adding an extra column to identify the content's catagory. The reason I want to do it this way, is so the manager of the CMS can add new sections, new pages in the CMS to represent them, and new tables wont have to be made, just a different content type in the content column I have in mind. Any problems with this design?

  4. #4
    Senior Member
    Join Date
    Nov 2001
    Posts
    1,255
    Originally posted here by Soda_Popinsky
    CREATE TABLE `scms_content` (
    `ID` int(11) NOT NULL auto_increment,
    `Poster` varchar(30) default NULL,
    `Title` varchar(100) default NULL,
    `TheDate` varchar(5) default NULL,
    `Message` text,
    `Link` varchar(100) NOT NULL default '',
    PRIMARY KEY (`ID`)
    ) TYPE=InnoDB AUTO_INCREMENT=2 ;
    Well, for the TheDate column, you should probably be using datetime instead of varchar(5) which in and of itself seems only large enough to handle MMDDY. Also, if this is a CMS system, are you considering an authentication mechanism? If so, you probably want Poster to be a foreign key to your auth table, and be of whatever is the simplest type that uniquely identifies users (sometimes an int ID type field).

    Another suggestion, not necessarily related to the DB, but maybe a link text field that will let you specify the text for the anchor tag separate from the URL.
    Chris Shepherd
    The Nelson-Shepherd cutoff: The point at which you realise someone is an idiot while trying to help them.
    \"Well as far as the spelling, I speak fluently both your native languages. Do you even can try spell mine ?\" -- Failed Insult
    Is your whole family retarded, or did they just catch it from you?

  5. #5
    Originally posted here by chsh
    [B]Well, for the TheDate column, you should probably be using datetime instead of varchar(5) which in and of itself seems only large enough to handle MMDDY.
    Easy enough, tx.

    Also, if this is a CMS system, are you considering an authentication mechanism? If so, you probably want Poster to be a foreign key to your auth table, and be of whatever is the simplest type that uniquely identifies users (sometimes an int ID type field).
    Well, all the posts are going to be made by the "admin". I haven't thought much of having multiple "admin's" or "mods" or anything that would require permissions (thinking about it in the future). Basically, the CMS is just a quick way to put data / news on the web for whatever purpose. There won't be many "users" however, because all the users will be admins, and everyone else is just viewing what the admin did, w/o interaction. Like a blog.

    I don't quite understand how this works, however. Can you explain what a foreign key is, why it is preferred over just having usernames in the table? I know we on AO all have ID #'s, maybe thats related somehow (Feel free to use AO as an example).

    Another suggestion, not necessarily related to the DB, but maybe a link text field that will let you specify the text for the anchor tag separate from the URL.
    What anchor tag? Sorry, but when you say anchor tag, I think of #whatever in the URL, and I don't see how that applies.

    Thanks a lot for the help!

  6. #6
    Senior Member
    Join Date
    Nov 2001
    Posts
    1,255
    Originally posted here by Soda_Popinsky
    I don't quite understand how this works, however. Can you explain what a foreign key is, why it is preferred over just having usernames in the table? I know we on AO all have ID #'s, maybe thats related somehow (Feel free to use AO as an example).
    Foreign keys are references to fields in other tables that are primary keys. Example:
    Table A has two fields: UserName and Password. Table B has three fields: MessageId, UserName and MessageText.
    ID is the primary key for Table A, while MessageId is the primary Key on Table B. Now, UserName in Table B is setup as a foreign key on Table A's UserName field, meaning that if an INSERT, or UPDATE takes place on Table B, it MUST contain a UserName field that exists in Table A, or it will fail. This is fundamental Referential Integrity, a rather important point of DB design.

    What anchor tag? Sorry, but when you say anchor tag, I think of #whatever in the URL, and I don't see how that applies.
    An anchor tag is what the <a href="http://some.url.org/some.html"> tag in HTML is. A stands for "Anchor". What I mean is if you store the link text separate from the URL, when you generate the anchor tag, it might look like: <a href="http://www.YourUrlHere.com">Link Text Here</a>. It's something you might want to consider.
    Chris Shepherd
    The Nelson-Shepherd cutoff: The point at which you realise someone is an idiot while trying to help them.
    \"Well as far as the spelling, I speak fluently both your native languages. Do you even can try spell mine ?\" -- Failed Insult
    Is your whole family retarded, or did they just catch it from you?

  7. #7
    Ok, makes sense. Thanks a lot.

  8. #8
    Webius Designerous Indiginous
    Join Date
    Mar 2002
    Location
    South Florida
    Posts
    1,123
    As far as the DATETIME is concerned:

    Are you going to be using this date time as a reference to the date you posted the content? Kind of like in the output of the article, news, post, etc:

    Posted on Thursday, October 28, 2004:

    Content here:



    If so you may want to just insert a php date() format as it is a bit more customizable than the DATETIME format of mysql.

    Just a thought.


    xmaddness
    Planet Maddness Industries
    http://www.planetmaddness.com

  9. #9
    That is currently what I am using, date(), but it seems more appropriate to use the datetime data type? I really don't know the benefits of that data type yet, but yes, I am using date() as of now so I can format the date better.

  10. #10
    Senior Member
    Join Date
    Nov 2001
    Posts
    1,255
    Originally posted here by xmaddness
    If so you may want to just insert a php date() format as it is a bit more customizable than the DATETIME format of mysql.
    Nonsense, you can format the datetime data however you like.

    Originally posted here by Soda_Popinsky
    That is currently what I am using, date(), but it seems more appropriate to use the datetime data type? I really don't know the benefits of that data type yet, but yes, I am using date() as of now so I can format the date better.
    The benefit is datetime is more portable across different languages because it is standard to the databse server. It can easily be converted into whatever output format you want to use once you have it in PHP. It is also faster to sort query output by a datetime datatype than it is to sort it by a varchar datatype.
    Chris Shepherd
    The Nelson-Shepherd cutoff: The point at which you realise someone is an idiot while trying to help them.
    \"Well as far as the spelling, I speak fluently both your native languages. Do you even can try spell mine ?\" -- Failed Insult
    Is your whole family retarded, or did they just catch it from you?

Posting Permissions

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