SitePoint Sponsor

User Tag List

Results 1 to 21 of 21
  1. #1
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

    Code:
    #
    # 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`
    #

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks a lot rudy

    you're always such a great help.

    I will follow your advice and post the result.

  4. #4
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    just a question about this:
    do not say NOT NULL and then allow empty strings
    I missed where I did that

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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 '',
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?


  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok I see... so how could I avoid a default string while not allowing empty strings?

    thx rudy.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok cool, so the 'problem' is solved then since I sanatize data

  11. #11
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

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


    Code:
    #
    # 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

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by duuudie
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  14. #14
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Me again
    Code:
    `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.

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    what does this from_unixtime do?????

    duuuuuuuuuuuuuuudie, allow me to introduce you to the mysql manual

    13.5 Date and Time Functions
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by duuudie
    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!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  18. #18
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  19. #19
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi

    thanks a lot for your reply.

    So how can I avoid that?

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...



Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •