#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2000
    Posts
    108
    Rep Power
    15
    Hello,
    I am attempting to create my first mySQL database (a very large one at that). I am not sure how to create it in the most efficient way, however.
    I have many fields called "group". Each "group" contains a number of each of "name", "URL", and "rating" fields (see picture below). Should I create many tables, each one a group, or should I somehow combine them all into one very large table? Is there a way to create arrays in mySQL?

    Thank you for any help in advance..
    Josh
    CATGOLF888@aol.com

    ====================================
    Each group looks like:

    Group
    Name URL Rating
    Name URL Rating
    Name URL Rating
    Name URL Rating
    Name URL Rating
    Name URL Rating

    Group 2
    Name URL Rating
    Name URL Rating
    Name URL Rating
    Name URL Rating
    Name URL Rating
    Name URL Rating

    and so on...
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 1999
    Location
    Annapolis, Maryland US
    Posts
    113
    Rep Power
    16
    It all depends on the data you're dancing with...post some examples of the values to be stored to give everyone a better idea of where you're coming from.

  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2000
    Posts
    108
    Rep Power
    15
    Here's an example (the data will hopefully become a list of links):

    Group: Search Engines
    (NAME) (RATING) (URL)
    Yahoo 5 http://www.yahoo.com
    HotBot 4.5 http://www.hotbot.com
    Excite 4.5 http://www.excite.com
    Altavista 4 http://www.altavista.com
    Lycos 4 http://www.lycos.com

    Group: Email Providers
    (NAME) (RATING) (URL)
    Hotmail 5 http://www.hotmail.com
    Yahoo 5 http://www.yahoo.com
    Bigfoot 4 http://www.bigfoot.com
    Zensearch 4 http://www.zensearch.com
    Mail 4 http://www.mail.com

    What would be the best way to database a large number of groups???
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 1999
    Location
    Annapolis, Maryland US
    Posts
    113
    Rep Power
    16
    OK, so from your examples, it looks like the group and url columns should be a composite primary key.

    Try something like

    create table table_name(
    Group varchar(25) not null,
    SiteName varchar(25) not null,
    Rating decimal(5,1),
    URL varchar(50),
    primary key(Group, SiteName));

    This will ensure that there are no duplicate Group-Sitename pairs in the table. You could put a primary key across all four columns if you wanted to, but the fewer columns in a composite index, the better performance (generally)

    Later
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 1999
    Location
    Annapolis, Maryland US
    Posts
    113
    Rep Power
    16
    Sorry about that, Group and SiteName should be the composite primary key
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2000
    Posts
    108
    Rep Power
    15
    Thanks. Would this be the most efficient way if I had more than 100 groups (1 per table)?? Why do I need the key?

    Thank you again.
    Josh
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 1999
    Location
    Annapolis, Maryland US
    Posts
    113
    Rep Power
    16
    I was assuming (maybe incorrectly) that you would have all the data in one large table and this would work as long as each group name is unique (Email Providers, Search Engines, Portals) of the 100 that you have. The composite primary key is necessary so that you don't have duplicate entries i.e,

    msyql>insert into table_name values(
    'Email Providers', 'Yahoo', 5, 'http://www.yahoo.com');
    // this insert statement is OK

    mysql>insert into table_name values('Search Engines', 'Yahoo', 5, 'http://www.yahoo.com');
    // this insert statement would be OK

    mysql>insert into table_name values('Email Providers', 'Yahoo', 4.5, 'http://www.yahoo.com');
    // ERROR - violates composite primary key constraint GroupName-SiteName 'Email Providers-Yahoo' already exists -> just use UPDATE to change info in this row

    I tend to think this table structure would be much more concise and manageable than having 100 different tables based on each group name
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2000
    Posts
    108
    Rep Power
    15
    Thanks so much for your help...

Similar Threads

  1. PHP with MySql with another database server
    By MrMaTriX in forum PHP Development
    Replies: 5
    Last Post: July 27th, 2004, 09:12 AM
  2. Creating Pages from a MySQL Database
    By JohnSaunders in forum PHP Development
    Replies: 16
    Last Post: September 25th, 2002, 10:35 AM
  3. Replies: 1
    Last Post: August 13th, 2002, 05:14 AM
  4. Getting Access to use MySQL
    By -=-steve-=- in forum MySQL Help
    Replies: 1
    Last Post: November 11th, 2001, 12:01 PM
  5. Replies: 1
    Last Post: October 2nd, 2000, 02:37 AM

IMN logo majestic logo threadwatch logo seochat tools logo