Results 1 to 10 of 10

Thread: Finalising a database design

  1. #1
    Senior Member Kezzer's Avatar
    Join Date
    Sep 2003
    Posts
    4,863
    Thanks
    12
    Thanked
    5 times in 5 posts

    Finalising a database design

    I'm thread starter of the week in this forum. As you can see from the following SQL, i've almost finished the SQL for my new database design. I just need to check the data types are correct for some of the un-finished ones.

    Code:
    CREATE TABLE `users` (
      `uid` int(10) NOT NULL default '0',
      `email` varchar(100) NOT NULL default '',
      `password` varchar(60) NOT NULL default '',
      `fname` varchar(30) NOT NULL default '',
      `lname` varchar(30) NOT NULL default '',
      PRIMARY KEY  (`uid`),
      UNIQUE KEY `email` (`email`)
    ) TYPE=MyISAM;
    
    CREATE TABLE `access` (
      `aid` int(10) NOT NULL default '0',
      `usergroupID` int(11) NOT NULL default '0',
      `access_type` int(3) NOT NULL default '0',
      PRIMARY KEY (`aid`)
    ) TYPE=MyISAM;
    
    CREATE TABLE `item` (
      `item_id` int(10) NOT NULL default '0',
      `title` varchar(50) NOT NULL default '',
      `date`
      `last_modified`
      `content`
      `user_id`
      `item_type_id`
      `media_location`
    ) TYPE=MyISAM;
    
    CREATE TABLE `item_type` (
      `item_type_id` int(10) NOT NULL default '0',
      `item_type`
    ) TYPE=MyISAM;
    I know there are people here with a lot of SQL knowledge (thanks to scotty for providing me with his database design which i hacked into something suitable for myself). I just need to know what data types should be used for the rest of the fields

    Cheers once again

  2. #2
    Senior Member GAteKeeper's Avatar
    Join Date
    Feb 2004
    Location
    Derbyshire, UK
    Posts
    584
    Thanks
    14
    Thanked
    34 times in 23 posts
    • GAteKeeper's system
      • Motherboard:
      • MSI P67-GD5
      • CPU:
      • Intel i7 2600k
      • Memory:
      • 8Gb Corsair DDR3 1600
      • Storage:
      • ~44TB
      • Graphics card(s):
      • 980Ti
      • PSU:
      • Seasonic S12 600W
      • Case:
      • Lian Li PC-65
      • Operating System:
      • Win10 64bit
      • Monitor(s):
      • Dell U3415W & 2405fpw
      • Internet:
      • 45Mb vDSL
    Why the inconsistant naming convention??

    The naming convention I use for all my databases is this:

    XXX_Table_name
    XXX_Column1
    XXX_Column2
    XXX_Column3
    etc

    where XXX is a TLA for the table

    for example

    USR_USERS
    USR_USER_ID
    USR_FIRST_NAME
    USR_SURNAME
    etc

    This is done for many reasons but the main ones are these:
    1)Each column is unique in the database - protects against crashes caused by sloppy SQL
    2)Allows the naming of columns with reserved keywords - in oracle date is a reserved keyword and cannot be used a column name without alot of hassle but USR_date is fine.

    other than that make sure your foriegn keys are keyed against an indexd column or enforcing the join will the very slow.

    GAteKEeper
    Keeper of the Gates of Hell

  3. #3
    Senior Member Kezzer's Avatar
    Join Date
    Sep 2003
    Posts
    4,863
    Thanks
    12
    Thanked
    5 times in 5 posts
    Cheers for the tip. Yeh my database knowledge isn't up to scratch so i don't know of things like this (although it's more common sense i think).

    I'll change the column names now.

    I don't suppose you know of what data types to use for the date ? I'm sure it was datetime but there's so many different ones available i'm never sure which is the best to use? This applies for content as well.

    In the item table those id's will be foreign right? I'm just touching up this design as my colleague was the one who knows things about databases but is unavailable at the moment so i have to finish it off

    Cheers for the reply to far though, much appreciated

  4. #4
    Goat Boy
    Join Date
    Jul 2003
    Location
    Alexandra Park, London
    Posts
    2,428
    Thanks
    0
    Thanked
    0 times in 0 posts
    What about integrity constraints? You'll need to change to InnoDB table types too...
    "All our beliefs are being challenged now, and rightfully so, they're stupid." - Bill Hicks

  5. #5
    I'd have a double vodka over a lager anyday
    Join Date
    Jan 2005
    Location
    Newcastle Upon Tyne
    Posts
    591
    Thanks
    6
    Thanked
    5 times in 4 posts
    Primary keys in 'users' and 'access' are specified to be NOT NULL. Primary keys are UNIQUE and NOT NULL by default.

  6. #6
    Member
    Join Date
    Jun 2005
    Posts
    99
    Thanks
    22
    Thanked
    0 times in 0 posts
    don't know if you know this or not but you will need foreign key for access/usergroup in user i think so can give access level or put them in usergroup.

    maybe upload you correct sql you have so we not looking at old stuff

  7. #7
    Senior Member Kezzer's Avatar
    Join Date
    Sep 2003
    Posts
    4,863
    Thanks
    12
    Thanked
    5 times in 5 posts
    Ah that's the latest i think, i haven't touched that design in a while I'll get back on it soon

  8. #8
    Theoretical Element Spud1's Avatar
    Join Date
    Jul 2003
    Location
    North West
    Posts
    7,508
    Thanks
    336
    Thanked
    320 times in 255 posts
    • Spud1's system
      • Motherboard:
      • Gigabyte Aorus Master
      • CPU:
      • 9900k
      • Memory:
      • 16GB GSkill Trident Z
      • Storage:
      • Lots.
      • Graphics card(s):
      • RTX3090
      • PSU:
      • 750w
      • Case:
      • BeQuiet Dark Base Pro rev.2
      • Operating System:
      • Windows 10
      • Monitor(s):
      • Asus PG35VQ
      • Internet:
      • 910/100mb Fibre
    also things like table names - i always use tbl_name to show its a table...i know its normally clear enough anyway but it does help to make it clear, especially when your doing the code for connecting to the db with ado etc, i find it helps me to make a few less mistakes

  9. #9
    Senior Member Kezzer's Avatar
    Join Date
    Sep 2003
    Posts
    4,863
    Thanks
    12
    Thanked
    5 times in 5 posts
    Hey sorry, time to revive this thread as i'm needing this database design.

    Instead of the one above i'm going for a more simpler approach to begin with (i have reasons)

    Anyway, the simplified version will post news only, here it is;

    Code:
    CREATE TABLE `sjcms_users` (
      `user_id` int(10) NOT NULL default '0',
      `email` varchar(100) NOT NULL default '',
      `password` varchar(60) NOT NULL default '',
      `first_name` varchar(30) NOT NULL default '',
      `last_name` varchar(30) NOT NULL default '',
      PRIMARY KEY  (`uid`),
      UNIQUE KEY `email` (`email`)
    ) TYPE=MyISAM;
    
    CREATE TABLE `sjcms_access` (
      `access_id` int(10) NOT NULL default '0',
      `usergroupID` int(11) NOT NULL default '0',
      `access_type` int(3) NOT NULL default '0',
      PRIMARY KEY (`aid`)
    ) TYPE=MyISAM;
    
    CREATE TABLE `sjcms_news` (
      `news_id` int(10) NOT NULL default '0',
      `date` datetime NOT NULL default '0000-00-00 00:00:00',
      `last_modified` datetime NOT NULL default '0000-00-00 00:00:00',
      `title` varchar(50) NOT NULL default '',
      `content` 
      `user_id`
    ) TYPE=MyISAM;
    I can't decide of what data type to go with for content. This is for general news remember, not masses of data. What's the best one to go for which is quickest to retrieve from the db?

    Cheers

  10. #10
    Seething Cauldron of Hatred TheAnimus's Avatar
    Join Date
    Aug 2005
    Posts
    17,168
    Thanks
    803
    Thanked
    2,152 times in 1,408 posts
    as people said table name _ rowname convention is very nice.

    It makes for much easyer to read code, and helps provent certain types of issue (as said above). Really can't recomend that enough.

    Content is going to be unicode full text? If you don't want to set a max length, thats probably the best way, as for how to declare that, and such depends on the database, apparently some prefer to use a BLOB because they won't index it, so if you never want to search on the contents of content, that might be the fastest way.
    throw new ArgumentException (String, String, Exception)

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. abnormal access database traffic?
    By Stoo in forum Networking and Broadband
    Replies: 8
    Last Post: 06-10-2004, 04:43 PM
  2. Interactive ASP database stuff
    By joshwa in forum Software
    Replies: 8
    Last Post: 31-03-2004, 02:36 PM
  3. fantastic new site design!
    By orgasmo in forum HEXUS Suggestions
    Replies: 6
    Last Post: 17-10-2003, 11:54 AM
  4. Give me some input on this design...
    By THCi in forum Software
    Replies: 15
    Last Post: 26-09-2003, 10:35 PM
  5. hexus database
    By ingouk in forum HEXUS Suggestions
    Replies: 4
    Last Post: 08-09-2003, 09:46 PM

Posting Permissions

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