Database Design

[Soda_Popinsky]

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!

[chsh]

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.

[Soda_Popinsky]

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?

[chsh]

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.

[Soda_Popinsky]

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!

[chsh]

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.

[Soda_Popinsky]

Ok, makes sense. Thanks a lot.

[xmaddness]

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

[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.

[chsh]

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.