About to start a project: need reviews and critics-->structure.

[duuudie]

Hi

I am about to work on a project. I thought it would be a good idea to submit the structure of my tables to you so you can tell me how to improve some things. There are 6 tables. I don't explain what each one of them do since it seems pretty clear. However, if you had any questions don't hesitate to ask me to clarify anything.

Thanks a lot for your time

#
# Table structure for table `categories`
#

CREATE TABLE `categories` (
  `catID` smallint(11) unsigned NOT NULL auto_increment,
  `position` smallint(6) NOT NULL default '0',
  `name` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`catID`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

#
# Dumping data for table `categories`
#


# --------------------------------------------------------

#
# Table structure for table `faq`
#

CREATE TABLE `faq` (
  `faqID` smallint(5) unsigned NOT NULL auto_increment,
  `title` varchar(50) NOT NULL default '',
  `text` text NOT NULL,
  `position` smallint(6) unsigned NOT NULL default '0',
  PRIMARY KEY  (`faqID`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

#
# Dumping data for table `faq`
#


# --------------------------------------------------------

#
# Table structure for table `texts`
#

CREATE TABLE `texts` (
  `textsID` mediumint(8) unsigned NOT NULL auto_increment,
  `title` mediumtext NOT NULL,
  `text` varchar(255) NOT NULL default '',
  `category` smallint(5) unsigned NOT NULL default '0',
  `postedDate` int(10) unsigned NOT NULL default '0',
  `readNumber` mediumint(8) unsigned NOT NULL default '0',
  `newAdmin` tinyint(3) unsigned NOT NULL default '0',
  `userID` mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (`textsID`),
  KEY `text` (`text`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

#
# Dumping data for table `texts`
#


# --------------------------------------------------------

#
# Table structure for table `textshighlights`
#

CREATE TABLE `textshighlights` (
  `textshighlightsID` mediumint(8) unsigned NOT NULL auto_increment,
  `textID` mediumint(8) unsigned NOT NULL default '0',
  `highlighterID` mediumint(8) unsigned NOT NULL default '0',
  `highlightDate` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`textshighlightsID`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

#
# Dumping data for table `textshighlights`
#


# --------------------------------------------------------

#
# Table structure for table `users`
#

CREATE TABLE `users` (
  `userID` int(11) unsigned NOT NULL auto_increment,
  `username` varchar(25) NOT NULL default '',
  `password` varchar(25) NOT NULL default '',
  `permission` tinyint(3) unsigned NOT NULL default '0',
  `registrationDate` int(10) unsigned NOT NULL default '0',
  `lastLogin` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`userID`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

#
# Dumping data for table `users`
#


# --------------------------------------------------------

#
# Table structure for table `usershighlights`
#

CREATE TABLE `usershighlights` (
  `usershighlightsID` mediumint(8) unsigned NOT NULL auto_increment,
  `userID` mediumint(8) unsigned NOT NULL default '0',
  `highlighterID` mediumint(8) unsigned NOT NULL default '0',
  `highlightDate` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`usershighlightsID`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

#
# Dumping data for table `usershighlights`
#

[r937]

do not use reserved words for column names

make sure primary keys and foreign keys have identical datatypes

use DATETIME instead of INTEGER for dates

don't bother to say UNSIGNED for auto_increment columns that would never reach their normal signed limit in your lifetime

do not assign strings to numeric columns

do not say NOT NULL and then allow empty strings


okay, those tips cover about 90% of your columns

let us know when you have the revision ready

[duuudie]

thanks a lot rudy

you're always such a great help.

I will follow your advice and post the result.

[duuudie]

just a question about this:

do not say NOT NULL and then allow empty strings

I missed where I did that

[r937]

yeah, hard to spot, isn't it

you only did it 4 times

CREATE TABLE `categories` (
`name` varchar(30) NOT NULL default '',

CREATE TABLE `faq` (
`title` varchar(50) NOT NULL default '',

CREATE TABLE `texts` (
`text` varchar(255) NOT NULL default '',

CREATE TABLE `users` (
`username` varchar(25) NOT NULL default '',
`password` varchar(25) NOT NULL default '',

[duuudie]

Ok so the problem is 'somewhere else'... I don't understand how I allow an empty string... Isn't it related to the way I check data before inserting it?

[r937]

actually any NOT NULL character column will allow an empty string, i was just referring to how you more or less start out on the wrong foot by making the empty string your default

of course, you would have to control it in your INSERT statements to prevent empty strings from actually getting in

[duuudie]

Ok I see... so how could I avoid a default string while not allowing empty strings?

thx rudy.

[r937]

the DEFAULT is assigned when a row is inserted and no value is given for that column

the way you avoid it is by ensuring that you insert a value

[duuudie]

ok cool, so the 'problem' is solved then since I sanatize data

[duuudie]

Ok here is the revised version:
just a few things regarding rudy's great advice:

--------------
do not use reserved words for column names
--->tried to fix it. It should be ok now.

make sure primary keys and foreign keys have identical datatypes
---->should be ok as well.

use DATETIME instead of INTEGER for dates
--->I store dates using the PHP date() function. It makes my life easier for calculation. Then I format the date to be displayed.

don't bother to say UNSIGNED for auto_increment columns that would never reach their normal signed limit in your lifetime
--->cool tip. However, since it 'doesn't' hurt, I let it like it was.

do not assign strings to numeric columns
--->I didn't see where I did that. Maybe my col names where a bit confusing? Could anyone point out where I amde this mistake? Was it just an advice?

do not say NOT NULL and then allow empty strings
--->As stated above, data will be sanatize and checked.

--------------

#
# Table structure for table `categories`
#

CREATE TABLE `categories` (
  `catID` smallint(11) unsigned NOT NULL auto_increment,
  `catPosition` smallint(6) NOT NULL default '0',
  `catName` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`catID`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

#
# Dumping data for table `categories`
#


# --------------------------------------------------------

#
# Table structure for table `faq`
#

CREATE TABLE `faq` (
  `faqID` smallint(5) unsigned NOT NULL auto_increment,
  `faqTitle` varchar(50) NOT NULL default '',
  `faqText` text NOT NULL,
  `faqPosition` smallint(6) unsigned NOT NULL default '0',
  PRIMARY KEY  (`faqID`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

#
# Dumping data for table `faq`
#


# --------------------------------------------------------

#
# Table structure for table `users`
#

CREATE TABLE `users` (
  `userID` mediumint(11) unsigned NOT NULL auto_increment,
  `username` varchar(25) NOT NULL default '',
  `password` varchar(25) NOT NULL default '',
  `permission` tinyint(3) unsigned NOT NULL default '0',
  `registrationDate` int(10) unsigned NOT NULL default '0',
  `lastLogin` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`userID`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

#
# Dumping data for table `users`
#


# --------------------------------------------------------

#
# Table structure for table `usershighlights`
#

CREATE TABLE `usershighlights` (
  `usershighlightsID` mediumint(8) unsigned NOT NULL auto_increment,
  `userID` mediumint(8) unsigned NOT NULL default '0',
  `highlighterID` mediumint(8) unsigned NOT NULL default '0',
  `highlightDate` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`usershighlightsID`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

#
# Dumping data for table `usershighlights`
#


# --------------------------------------------------------

#
# Table structure for table `userstexts`
#

CREATE TABLE `userstexts` (
  `textID` mediumint(8) unsigned NOT NULL auto_increment,
  `textTitle` mediumtext NOT NULL,
  `textText` varchar(255) NOT NULL default '',
  `category` smallint(5) unsigned NOT NULL default '0',
  `postedDate` int(10) unsigned NOT NULL default '0',
  `readNumber` mediumint(8) unsigned NOT NULL default '0',
  `newAdmin` tinyint(3) unsigned NOT NULL default '0',
  `userID` mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (`textID`),
  KEY `text` (`textText`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

#
# Dumping data for table `userstexts`
#


# --------------------------------------------------------

#
# Table structure for table `userstextshighlights`
#

CREATE TABLE `userstextshighlights` (
  `textshighlightsID` mediumint(8) unsigned NOT NULL auto_increment,
  `textID` mediumint(8) unsigned NOT NULL default '0',
  `highlighterID` mediumint(8) unsigned NOT NULL default '0',
  `highlightDate` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`textshighlightsID`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

#
# Dumping data for table `userstextshighlights`
#

Thanks a lot for your time and your energy

[r937]

use DATETIME instead of INTEGER for dates
--->I store dates using the PHP date() function. It makes my life easier for calculation. Then I format the date to be displayed.

your life will not be easier because you will always have to convert it

compare

select userid, registrationDate from ... where ...

with

select userid, from_unixtime(registrationDate) from ... where ...

if you enjoy the extra coding, be my guest

do not assign strings to numeric columns
--->I didn't see where I did that. Maybe my col names where a bit confusing? Could anyone point out where I amde this mistake?

`catPosition` smallint(6) NOT NULL default '0',
`faqPosition` smallint(6) unsigned NOT NULL default '0',
`permission` tinyint(3) unsigned NOT NULL default '0',
`registrationDate` int(10) unsigned NOT NULL default '0',
`lastLogin` int(10) unsigned NOT NULL default '0',
`userID` mediumint(8) unsigned NOT NULL default '0',
`highlighterID` mediumint(8) unsigned NOT NULL default '0',
`highlightDate` int(10) unsigned NOT NULL default '0',
`category` smallint(5) unsigned NOT NULL default '0',
`postedDate` int(10) unsigned NOT NULL default '0',
`readNumber` mediumint(8) unsigned NOT NULL default '0',
`newAdmin` tinyint(3) unsigned NOT NULL default '0',
`userID` mediumint(8) unsigned NOT NULL default '0',
`textID` mediumint(8) unsigned NOT NULL default '0',
`highlighterID` mediumint(8) unsigned NOT NULL default '0',
`highlightDate` int(10) unsigned NOT NULL default '0',

also, regarding the number in parentheses behind you numeric datatypes, i would leave those off, they deal only with the number of zeroes to be printed in front of the number when using the ZEROFILL option, they have nothing to do with how big a number you want to store

[duuudie]

Hi rudy
I think I need some more explanations about this:

select userid, from_unixtime(registrationDate) from ... where ...

what is exactly the difference? What does this from_unixtime do?

thanks a lot

[duuudie]

Me again

`catPosition` smallint(6) NOT NULL default '0',
`faqPosition` smallint(6) unsigned NOT NULL default '0',
`permission` tinyint(3) unsigned NOT NULL default '0',
`registrationDate` int(10) unsigned NOT NULL default '0',
`lastLogin` int(10) unsigned NOT NULL default '0',
`userID` mediumint(8) unsigned NOT NULL default '0',
`highlighterID` mediumint(8) unsigned NOT NULL default '0',
`highlightDate` int(10) unsigned NOT NULL default '0',
`category` smallint(5) unsigned NOT NULL default '0',
`postedDate` int(10) unsigned NOT NULL default '0',
`readNumber` mediumint(8) unsigned NOT NULL default '0',
`newAdmin` tinyint(3) unsigned NOT NULL default '0',
`userID` mediumint(8) unsigned NOT NULL default '0',
`textID` mediumint(8) unsigned NOT NULL default '0',
`highlighterID` mediumint(8) unsigned NOT NULL default '0',
`highlightDate` int(10) unsigned NOT NULL default '0',

Once again, the problem is 'somewhere else'. I don't understand where and how I assign string values to numeric values. I will only record numbers in those fields. What am I missing?

also, regarding the number in parentheses behind you numeric datatypes, i would leave those off, they deal only with the number of zeroes to be printed in front of the number when using the ZEROFILL option, they have nothing to do with how big a number you want to store

I am using phpmyadmin and it seems that it adds these numbers automatically no matter what I do. I erase them, save the the new field and... they appear again. What should i do to avoid that?

Once again all I can say is thank you. You're a great help rudy. thumbs up.

[r937]

what does this from_unixtime do?????

duuuuuuuuuuuuuuudie, allow me to introduce you to the mysql manual

13.5 Date and Time Functions

[r937]

I am using phpmyadmin and it seems that it adds these numbers automatically no matter what I do. I erase them, save the the new field and... they appear again.

alex, i'm sorry, i didn't know it does that!

bad, bad phpmyadmin -- no soup for you, one year!!

[duuudie]

lol, yeah it's strange that it does that Maybe that I am missing something.

Thanks a lot for the link rudy, will read the MySql manual asap (once I will be done with the PHP manual).

Other than that, does it sound good to you? Can I start to code without facing major problems?

[guelphdad]

duuudie

when you are doing this:
`highlightDate` int(10) unsigned NOT NULL default '0',

you aren't assigning the numeral 0, your are assigning the string '0',
which is two different things. MySQL will have to figure out if you are doing a numerical computation and convert the string first in order to do the computation.

[duuudie]

Hi

thanks a lot for your reply.

So how can I avoid that?

[r937]

yeah, but dave, he says phpmyadmin does it to him as a "feature"

how do you turn that off?

there's a checkbox for "Enclose table and field names with backquotes" but apparently string defaults for numeric fields are perfectly okay...

[duuudie]

Well, I think I will go like that... I will try to find a phpmyadmin forum and ask how I could avoid that... Or... I could try to recreate my tables with a statement like this one:
`highlightDate` int(10) unsigned NOT NULL default '0',
or I could learn how to work with mysql without phpmyadmin...