CODEWITHSUNDEEP
phpmysqlcontent-management-systemtypesphpmyadmin
Moshe
Moshe

Reputation: 58087

MySQL datatypes?

I'm designing a database in MySQL and PHP for a basic CMS. The CMS will have a front end which will allow sorting and searching. The backend will allow authorized users to upload files.

I'm using PHPMyAdmin and I'd like help setting up my database.

I am open to answers explaining various MySQL datatypes and what they are good for as well . Use common database fields as examples please.

Below is a list of what I'd like. What's missing and what datatypes do I need to use?

Resources (For my files)

  • file_id
  • filename (Files are presorted and display names and paths are derived from here.)
  • file_type (PDF | AUDIO | VIDEO | PHOTO [Also used to generate file urls])
  • upload date (timestamp in PHP or MySQL)
  • uploaded_by (User ID from Users table)
  • event (event_id from Events table, optional)

Users (User accounts - for admin access and maybe a notification list)

  • user_id
  • first_name
  • last_name
  • email
  • password
  • phone_number (optional)
  • permissions_level (read only, upload)
  • creation_date

Events

  • event_id
  • event_name
  • event_location
  • event_date
  • event_description
  • entry_date

Upvotes: 1

Views: 500

Answers (3)

2ndkauboy
2ndkauboy

Reputation: 9387

What I would take:

Resources (For my files)

  • file_id INT (or SMALLINT depending on the number of expected entries)
  • filename VARCHAR (or text if longer than 255 chars)
  • file_type ENUM (if only those you mentioned or VARCHAR if dynamic types can be added)
  • upload DATE DATETIME (or DATE if you don't need the time)
  • uploaded_by INT (or SMALLINT but the same as in the user table)
  • event INT (or SMALLINT but the same as in the event table)

Users (User accounts - for admin access and maybe a notification list)

  • user_id INT (or SMALLINT depending on the number of expected entries)
  • first_name VARCHAR
  • last_name VARCHAR
  • email VARCHAR
  • password CHAR(40) (for a SHA1 hash)
  • phone_number VARCHAR (as it might contain something like -, / or +)
  • permissions_level TINYINT (if only number values and at most 127 values)
  • creation_DATE DATETIME (or DATE if you don't need the time)

Events

  • event_id INT (or SMALLINT depending on the number of expected entries)
  • event_name VARCHAR
  • event_location VARCHAR
  • event_DATE DATETIME (or DATE if you don't need the time)
  • event_description TEXT (as 255 of VARCHAR might be to short)
  • entry_DATE DATETIME (or DATE if you don't need the time)

When you have set up your database and input some dummy data, you can run a simple statement through phpmyadmin that will tell you, what MySQL would take for that exact dummy data:

SELECT * FROM events PROCEDURE ANALYSE()

In the column Optimal_fieldtype you will find what MySQL tells you to take. But you should not take that exact fieldtype. It will tell you very often to take a ENUM but most of the time you add random data so you have to take a VARCHAR in that cases the column Max_length will give you a hint on how long it should be. But on all VARCHAR fields you should add additonal space depending on how long you expect the values to be. Take in consideration that even a name can be longer than 50 chars.

Upvotes: 2

Jim
Jim

Reputation: 18853

What is missing, well that is sort of for you to decide / what is required. You should read up on Designing databases.

As far as the datatypes for what you have

  • The id fields should be a INT, or BIGINT (depends on how big your application may become) and set as the PRIMARY KEY.

  • The names should be a varchar how long you want it depends on what your requirements are. Most first / list names are generally 25-30 characters max. Event names could be upwards to 250, depending on your requirements.

  • The location will be similar to the name as a VARCHAR somewhere around 50-150, depending on your requirements.

  • The date should be a DATETIME field.

  • The description should be either a VARCHAR(250) or a TEXT field.

  • The permissions really depends on how you want to handle it. This could be an INT or a VARCHAR (incase you want to serialize an array).

  • Phone number could be an INT (if you want to strip all non-numeric characters and format it your own way) or a VARCHAR(15) if you do not want to strip the characters

  • EMail should be a VARCHAR(250).

Hopefully this helps, again it really depends on your requirements for the application and what you have envisioned. But the initial types can always be changed as your requirements change.

EDIT

And if you want to know full information about the different MySQL Data Types, read the manual: http://dev.mysql.com/doc/refman/5.0/en/data-types.html

Upvotes: 0

GrandmasterB
GrandmasterB

Reputation: 3455

Your user table doesnt have a column for the password hash. Not sure if you intended for it to have such or not. I cant see how we can answer what datatypes should be used, since it completely depends on how you plan on using the columns. For dates, I prefer datetimes over timestamps, but thats just a personal preference as I like to manually insert the dates in the queries.

Upvotes: 1

Related Questions