How to structure my tables?

[Andy82]

Hey,

 

After messing around with PHP and MySQL for a little while I am trying to make my first CMS. It is going to be used for media i.e. Games, Videos, Jokes, Images and maybe more.

 

I want it so I can select the last [X] amount of Media (Games, Videos, Jokes, and Images) added in the last [X] amount of time and order them based e.g. on a timestamp. The thing that I am concerned about is if I have say 5000 rows in each, I will need to select all 20,000 results to get only a few results.

 

How should the tables be structured? Should I have 1 for everything and then define the type in a field? or Should I have a separate each into it own table e.g. Games, Videos etc?

 

Any help would be greatly appreciated.

 

Andy

[fenway]

Why does everyone want to re-invent the wheel... sigh.

[Andy82]

Why does everyone want to re-invent the wheel... sigh.

 

I know there are premade scripts out that do this. The only reason I wanted to use this as a basis for my first attempt is it is something that I am interested in. It was either this or something gaming related like a cheat site...but there are even more of those.

[corbin]

Read up on database normalization.

 

 

 

For example, you have objects.  These objects have a property of belonging to a category (category such as Video Game, movie so on).  That means categories should be individual too.

 

 

So for example:

 

CREATE TABLE things (

    thing_id INT AUTO_INCREMENT PRIMARY KEY,

    thing_name varchar(255),

    thing_desc text,

    cat_id INT DEFAULT 0

);

 

CREATE TABLE categories (

    cat_id INT AUTO_INCREMENT PRIMARY KEY,

    parent_id INT default -1,

    cat_name varchar(255),

    cat_desc text

);

[Barand]

Why does everyone want to re-invent the wheel... sigh.

 

I have a standard answer to that one:

 

"If no-one ever re-invented the wheel, our cars would be running on discs of granite. And in that re-inventing of wheels they also invented pneumatic tyres, differentials, disc brakes etc.

Just because something exists doesn't mean it's perfect."

 

And that's apart from the learning exercise of developing an application.

[tomfmason]

So for example:

 

CREATE TABLE things (

    thing_id INT AUTO_INCREMENT PRIMARY KEY,

    thing_name varchar(255),

    thing_desc text,

    cat_id INT DEFAULT 0

);

 

CREATE TABLE categories (

    cat_id INT AUTO_INCREMENT PRIMARY KEY,

    parent_id INT default -1,

    cat_name varchar(255),

    cat_desc text

);

 

I have always done it more like this

 

CREATE TABLE things (

    id INT AUTO_INCREMENT PRIMARY KEY,

    name varchar(255),

    desc text,

    category_id INT DEFAULT 0

);

 

CREATE TABLE categories (

    id INT AUTO_INCREMENT PRIMARY KEY,

    parent_id INT default -1,

    name varchar(255),

    desc text

);

 

Most frameworks also follow the above conventions.

[corbin]

Hrmmm never knew there were 'conventions' for column names.

 

 

Do you mean to not prefix it like I did (cat_) or do you mean to have the column names similar?  Guessing you mean without prefixes.  Sometimes I add prefixes; sometimes I don't.  Guess it depends on my mood lol.

[fenway]

I prefer having the PK and FK named the same...

 

@Barand: the wheel analogy is only useful for the first few iterations.

[Andy82]

EDIT: Quoted the wrong person. Will change it after some sleep  !

 

@fenway: How can I learn making my first PHP CMS without making something that has been made? This is something that will be made only for me to learn how stuff works  .

[corbin]

I prefer having the PK and FK named the same...

 

@Barand: the wheel analogy is only useful for the first few iterations.

 

If that was aimed at me, I do too.  In my example they were named the same.  Hrmmmm thinking it wasn't aimed at me now.

 

 

EDIT: Quoted the wrong person. Will change it after some sleep  !

 

@fenway: How can I learn making my first PHP CMS without making something that has been made? This is something that will be made only for me to learn how stuff works  .

 

How do you learn how to make something?  You LEARN!  Learn PHP.  Learn SQL.  Put them together.  It's really not hard, it just takes some time and actually thinking.  If I asked someone how to learn to solve quadratic equations, wouldn't he/she tell me to LEARN how to solve quadratic equations?  (Yes, I realize those are quite simple.  Just throwing that out there as an example.)  The internet is an amazing resource, and a ton of very good PHP tutorials have been written.  Just look around.  Google is your friend.  (Some horrendous PHP articles have been written too though....  I would try to avoid anything older than a couple years.  Also, never trust just one source unless it's something common sense.)