perfect database design

Question

This is my table schema,

below am is the schema for feedback table functionality, please give good suggestion for the length and naming sense for the table and its fields,

please share your suggestion about the table schema and its length and naming conventions.

CREATE TABLE `mytest`.`tbl_feedback` (
`feedback_id` INT( 30 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`first_name` VARCHAR( 50 ) NOT NULL ,
`last_name` VARCHAR( 50 ) NOT NULL ,
`email_id` VARCHAR( 150 ) NOT NULL ,
`comment` TEXT NOT NULL ,
`cur_timestamp` VARCHAR( 20 ) NOT NULL ,
`ipaddress` VARCHAR( 20 ) NOT NULL ,
`status` INT( 3 ) NOT NULL DEFAULT '0'
) ENGINE = MYISAM ;

also i saw in this thread varbinary,

here they said use varbinary isntead of varchar, why should we go for varbinary, what is the advantage

This completely depends on your data. What we "think about it" is irrelevant. The only thing I can say is that prefixing table names with `tbl_` is redundant - they are all tables. — Wesley Murch, Apr 22 '11 at 03:47
@Madmartigan i change tbl_feedback to feedback, name it as feedback for Feedback, which is good advise, peoples likes which way. — vlk, Apr 22 '11 at 03:51
I'd use a different data type for your cur_timestamp other than varchar. Use something like DATE or TIMESTAMP. — justinl, Apr 22 '11 at 04:07
am using php, am going to do some check with php time(), if i use the mysql timestamp, then i guess there we will meet some conflic, can u give any suggestion for this — vlk, Apr 22 '11 at 06:31

score 1 · Accepted Answer · answered Apr 22 '11 at 04:11

1

Try to place fixed length fields (such as 'status') before the variable length fields. This won't make any difference whatsoever to the logic, but the program should run slightly faster when accessing those fields.

answered Apr 22 '11 at 04:11

No'am Newman

6,395
5
38
50

fixed length fields, am not get clear, can u please provide one example. – vlk Apr 22 '11 at 06:35
Status is an integer, so it is fixed size. Dates, not that you have one here, are fixed size. All the 'varchars', and especially the 'comment' field have unpredictable lengths, meaning that the database engine can't easily tell where one tuple ends and another begins. At the moment I can't find a reference for this. – No'am Newman Apr 24 '11 at 14:50

score 1 · Answer 2 · answered Apr 22 '11 at 04:15

Here are just a few observations:

Table name "tbl_feedback" - This is a personal preference, but I would remove the "tbl_" prefix - I've never run into a case where I've found it useful to have prefixes on database objects.
email_id - I would take off the "_id" suffix. ID is a common naming convention for key columns (primary or foreign), and you seem to be using that convention too. In your case, it doesn't seem like email_id is a foreign key though, so just "email" might be better.
cur_timestamp - A name like "created_date" or "updated_date" might make a little more sense. With cur_timestamp, it's not really clear what the column is for, or if you're supposed to update it when you update the record... etc. Also, for this column, it might be useful to use a built-in type that can represent a date, rather than a varchar.
ipaddress - Just a nitpick, but based on your naming convention, it might be better if this was "ip_address". Also, you probably want to make this column at least 40 characters or bigger, so you can store IPv6 addresses (in the common notation).
status - does this column point to another "status" table? If so, should this be a foreign key named "status_id"?

hi @Andy White , here status_id just for future purpose, at present acting like flag 0 or 1. ok i make it as status. — vlk, Apr 22 '11 at 06:34

score 0 · Answer 3 · answered Apr 22 '11 at 03:47

Some of the fields look they the could be normalized.. like status as foreign key constraint, to a status table, and people as a separate table with a personid as a FK. Then you could do cool things like look up all feedback by person, or delete all by person, etc.

perfect database design

3 Answers3