Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Location
    Maine
    Posts
    18
    Rep Power
    0

    Looking for review for efficiency and stability - first timer


    Hello Devshed,

    I'm a bit new with the boundaries of php and mysql and I'm trying to build a small, but high yield data system. I'm not doubting how the code will go in, but more so if it's stable and I'm linking correctly using foreign keys.

    The objective is to create:
    * 2 different types of users: those who start an account (admin) and those who enter the additional data (user)
    * User will populate most of the variable data and will be able to edit about 90% of it (admin will setup the initial stage and build a user for them to log in with)

    Note: I've never built a system that used foreign keys before.

    First I started reviewing a secure login (great tutorial: How to program a basic but secure login system using PHP and MySQL at Programming Languages > PHP Development > PHP FAQs and Stickies).

    Now I've pulled all the needed data and I'm trying to make sure they are linked correctly and efficiently and I thought I'd reach out to you guys to see if you notice any discrepancies. I wouldn't bother asking without trying, but I know I can build the database, I just want to make sure the foundation is as efficient and stable as possible before I get too much data in it, so I'm looking for any feedback or mistakes I've made so far trying to put this together.

    I appreciate any feedback on this.

    Code:
    --------------------------------
    Table 1: user data
    --------------------------------
    
    CREATE TABLE users (
      id int(15) NOT NULL AUTO_INCREMENT,
      acctnam varchar(128) COLLATE utf8_unicode_ci NOT NULL,
      acctid int(12) COLLATE utf8_unicode_ci NOT NULL,
      realname varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      usrname varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      pword char(64) COLLATE utf8_unicode_ci NOT NULL,
      salt char(16) COLLATE utf8_unicode_ci NOT NULL,
      email varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      usrlvl char(1) COLLATE utf8_unicode_ci NOT NULL,
      PRIMARY KEY (acctid),
           added PRIMARY KEY (acctnam),
      INDEX (acctnam),
            removed   INDEX (acctid),
      INDEX (realname),
      INDEX (email),
      UNIQUE KEY usrname (usrname),
      UNIQUE KEY email (email)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
    
    
    --------------------------------
    Table 2: accounts
    --------------------------------
    
    CREATE TABLE accounts (
      acctid int(12) COLLATE utf8_unicode_ci NOT NULL,
      FOREIGN KEY (acctid) references users(acctid) ON DELETE SET NULL,
      pocnam varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      pocph int(13) COLLATE utf8_unicode_ci NOT NULL,
      pocem varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      setupdate date COLLATE utf8_unicode_ci NOT NULL,
      PRIMARY KEY (acctid),
      INDEX (pocnam),
      INDEX (pocem)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
    
    
    --------------------------------
    Table 3: data
    --------------------------------
    
    CREATE TABLE data (
      acctid int(12) COLLATE utf8_unicode_ci NOT NULL,
      FOREIGN KEY (acctid) references accounts(acctid) ON DELETE SET NULL,
      acctnam varchar(128) COLLATE utf8_unicode_ci NOT NULL,
      FOREIGN KEY (acctnam) references users(acctnam) ON DELETE SET NULL,
      data_type varchar(128) COLLATE utf8_unicode_ci NOT NULL,
      sub_type_1 varchar(128) COLLATE utf8_unicode_ci NOT NULL,
      sub_type_2 varchar(128) COLLATE utf8_unicode_ci NOT NULL,
      data1 varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      data2 varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      data3 varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      INDEX (acctid),
      INDEX (acctnam),
      INDEX (data_type),
      INDEX (sub_type_1),
      INDEX (sub_type_2),
      INDEX (data1),
      INDEX (data2),
      INDEX (data3)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
    Thanks for peeking.
    Last edited by SiLeNCeD; November 21st, 2012 at 12:19 PM. Reason: Correcting subject to be more accurate
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    your FK from accounts.acctid referencing users.acctid will not work -- users.acctid has to be either the PK or have a UNIQUE index

    same goes for data.acctnam referencing users.acctnam

    i'm a little confused as to why these three are separate tables (it looks like they can be combined)

    also why data.acctid references accounts, while data.acctnam references users

    finally, if you have a column declared as PK (in users table), then declaring an INDEX on that same column is redundant overhead

    Comments on this post

    • SiLeNCeD agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Location
    Maine
    Posts
    18
    Rep Power
    0
    Hello r937.

    In your first response:
    Originally Posted by r937
    your FK from accounts.acctid referencing users.acctid will not work -- users.acctid has to be either the PK or have a UNIQUE index
    users.acctid is a PK (just lower down the line). Or did I need to define it differently? (Currently "PRIMARY KEY (acctid)")

    Originally Posted by r937
    same goes for data.acctnam referencing users.acctnam
    I definitely missed it for this one. I thought only 1 PK was necessary per table, but that leads me to my next question about your next quote.

    Originally Posted by r937
    i'm a little confused as to why these three are separate tables (it looks like they can be combined)
    I thought combining them would make a single table to large. Do you think I should combine them? I'm estimating probably about 5000 accounts (over the next 3 to 5 years) with quite a bit of data attached to each. (obviously after I get all of the bugs, like my current setup, out of the way lol)

    Originally Posted by r937
    also why data.acctid references accounts, while data.acctnam references users
    I don't have a good answer other then I thought crossreferencing them would be assistive for searches, but I'm no pro and my thoughts on this may have been way off.

    Originally Posted by r937
    finally, if you have a column declared as PK (in users table), then declaring an INDEX on that same column is redundant overhead
    I will remove the INDEX reference in that case. I have much to learn about the relationships. I've built smaller tables, but this is the first to really have relations between multiple tables.

    Thanks a lot for the feedback and corrections. I'll wait to hear what your thoughts are on the combining part.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    Originally Posted by SiLeNCeD
    I thought combining them would make a single table to large. Do you think I should combine them?
    unless you have another reason for splitting them up, yes

    with the proper indexes to optimize your queries, no table is "too large"

    and with a single table, your queries are more efficient and less complex

    Comments on this post

    • SiLeNCeD agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Location
    Maine
    Posts
    18
    Rep Power
    0
    Originally Posted by r937
    unless you have another reason for splitting them up, yes

    with the proper indexes to optimize your queries, no table is "too large"

    and with a single table, your queries are more efficient and less complex
    Again, I definitely appreciate the input. Let me rebuild it without the split and get back to you (should have up shortly). Thanks again.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Location
    Maine
    Posts
    18
    Rep Power
    0

    Corrections


    So I've combined the tables per r937's input and relabled the table as cms (content management system).

    The objective in this setup is to be able to search everything correctly as well, which is why there's as many indexes as there are. I also left spacing just to see where the major breaks were in the original tables and removed the duplicate entries.

    So without breaking up the tables, this should be very efficient and stable along with searchable criteria. Should I remove the indexes from the actual data_1/2/3? Or keep them there for quicker responses to searches?

    Code:
    CREATE TABLE cms (
      id int(15) NOT NULL AUTO_INCREMENT,
      acctnam varchar(128) COLLATE utf8_unicode_ci NOT NULL,
      acctid int(12) COLLATE utf8_unicode_ci NOT NULL,
      realname varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      usrname varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      pword char(64) COLLATE utf8_unicode_ci NOT NULL,
      salt char(16) COLLATE utf8_unicode_ci NOT NULL,
      email varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      usrlvl char(1) COLLATE utf8_unicode_ci NOT NULL,
    
      pocnam varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      pocph int(13) COLLATE utf8_unicode_ci NOT NULL,
      pocem varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      setupdate date COLLATE utf8_unicode_ci NOT NULL,
    
      data_type varchar(128) COLLATE utf8_unicode_ci NOT NULL,
      sub_type_1 varchar(128) COLLATE utf8_unicode_ci NOT NULL,
      sub_type_2 varchar(128) COLLATE utf8_unicode_ci NOT NULL,
      data_1 varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      data_2 varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      data_3 varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      PRIMARY (id,acctid),
      INDEX (acctnam), /* changed from PRIMARY (acctnam) */
      UNIQUE KEY usrname (usrname),
      UNIQUE KEY email (email),
      INDEX (realname),
      removed INDEX (email),
      INDEX (pocnam),
      INDEX (pocem),
      INDEX (data_type),
      INDEX (sub_type_1),
      INDEX (sub_type_2),
      INDEX (data_1),
      INDEX (data_2),
      INDEX (data_3)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    you've got PRIMARY KEY twice... i don't think that will work

    also, you've got UNIQUE KEY email (email), and then INDEX (email), which is redundant

    oh, and if you want to use AUTO_INCREMENT, then that column has to be (part of) the primary key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Location
    Maine
    Posts
    18
    Rep Power
    0
    Originally Posted by r937
    you've got PRIMARY KEY twice... i don't think that will work

    also, you've got UNIQUE KEY email (email), and then INDEX (email), which is redundant

    oh, and if you want to use AUTO_INCREMENT, then that column has to be (part of) the primary key
    Ha... Thanks. You mentioned that originally, I just missed the second email reference. I updated the code to show the corrections of what you mentioned.

    As for having 2 primary keys, I wanted to be sure and when I checked mysql manual (http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html), they have an example of PRIMARY KEY (grp, id) and mentioned that if I INDEX id, then it wouldn't increment correctly. Have you seen it used like that before? I updated the code for it like that, but that's something I can test once I get it in there.

    Thanks again... I'll be heading out soon, so if I don't check back tonight, you all have a Great Thanksgiving.
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    the example in the manual with a 2-column PRIMARY KEY is intended to show you how you can have a "subordinate" auto_increment column that increments starting with 1 for each new value of the higher key column

    compound primary keys are quite common, and only sometimes are integer ids used for them

    p.s. thanks, i've got tomorrow off, will be watching 3 entire nfl games (minus commercials, because i record and then fast-forward)

    but my thanksgiving was 6 weeks ago
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Location
    Sydney Australia
    Posts
    184
    Rep Power
    84
    Originally Posted by r937
    auto_increment column that increments starting with 1 for each new value of the higher key column
    That used to be true for MyISAM tables, but not InnoDB tables.
    I just tried it with MySQL V5.5 and it doesn't work with MyISAM now either.

    Code:
    CREATE TABLE `autoid` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `extra` varchar(3) NOT NULL,
      PRIMARY KEY (`id`,`extra`)
    ) ENGINE=MyISAM ;
    
    insert into autoid(extra) values('mon'), ('mon'),('tue'),('wed'),('mon');
    
    mysql> select * from autoid;
    +----+-------+
    | id | extra |
    +----+-------+
    |  1 | mon   |
    |  2 | mon   |
    |  3 | tue   |
    |  4 | wed   |
    |  5 | mon   |
    +----+-------+
    I would have expected the output to be
    Code:
    mysql> select * from autoid;
    +----+-------+
    | id | extra |
    +----+-------+
    |  1 | mon   |
    |  2 | mon   |
    |  1 | tue   |
    |  1 | wed   |
    |  3 | mon   |
    +----+-------+
    
    mysql> select version();
    +-----------+
    | version() |
    +-----------+
    | 5.5.17    |
    +-----------+
    edit:
    Just discovered that the PRIMARY KEY has to be defined with auto_increment column second in the compound key, and then it works, but only for MyISAM.

    This now exhibits the expected behaviour.

    Code:
    CREATE TABLE `autoid` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `extra` varchar(3) NOT NULL,
      PRIMARY KEY (`extra`,`id`)
    ) ENGINE=MyISAM ;
    
    mysql> insert into autoid(extra) values('mon'), ('mon'),('tue'),('wed'),('mon');
    
    Query OK, 5 rows affected (0.00 sec)
    Records: 5  Duplicates: 0  Warnings: 0
    
    mysql> select * from autoid;
    +----+-------+
    | id | extra |
    +----+-------+
    |  1 | mon   |
    |  2 | mon   |
    |  3 | mon   |
    |  1 | tue   |
    |  1 | wed   |
    +----+-------+
    5 rows in set (0.00 sec)
    Last edited by BarryG; November 21st, 2012 at 10:28 PM.
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Location
    Maine
    Posts
    18
    Rep Power
    0

    Tables


    So I think I have this figured out and I'm about to implement building the db.

    @r937: You mentioned I should combine the tables, so that's what I'm going to do, but I've decided that I'm going to make the actual users who have access to the system separate from the data I'm collecting. In doing this, I have a couple questions.

    1. Combining: The data is all in 1 table and that will reference the account number the user is assigned in the users table (which will keep them locked to only the data referencing the same account number in the data table). I created a spreadsheet with what the db will look like and on a small setup, this will take up to 80 to 100 rows. On a large setup, it may take up to 200 rows per account. After a few hundred setups, won't the searching start getting really slow? I'm only going to index a single column containing the actual data values that are submitted per account.
    2. As mentioned above, I started my php script using the Secure Login link. I'm going to add user levels to that script. In order to give an admin full access to searching all of the data, should I just add a reference to the session that checks for a user level (like a for admin or n for normal) and then on that value, build a restricting for normal users if the value is n? (ex: n = only pull data for users account number [acct#: 1234])


    Hopefully I'm making sense here lol. Thanks.
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Location
    Maine
    Posts
    18
    Rep Power
    0

    Not working


    After going thru and reorganizing the 2 tables, I'm finding that the primary key is acting as a unique and not allowing duplicate values between users. Example: I tried setting a universal number of 1111111 for any admin, but if I add this for the acct for each user, the 2nd attempt gives me this error:

    SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1111111' for key 1

    Here's the final output of the tables. I guess I need to restrategize on how the values work properly again (I think I'm missing something here).

    Code:
    CREATE TABLE `users` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      `password` char(64) COLLATE utf8_unicode_ci NOT NULL,
      `name` char(64) COLLATE utf8_unicode_ci NOT NULL,
      `salt` char(16) COLLATE utf8_unicode_ci NOT NULL,
      `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      `userlvl` char(1) COLLATE utf8_unicode_ci NOT NULL,
      `acct` int(12) COLLATE utf8_unicode_ci NOT NULL,
      PRIMARY KEY (`acct`),
      UNIQUE KEY `id`,
      UNIQUE KEY `username` (`username`),
      UNIQUE KEY `email` (`email`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
    
    
    CREATE TABLE `data` (
      `acct_ref` int(11) NOT NULL,
      `data_type` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      `sub_dtype_1` char(64) COLLATE utf8_unicode_ci NOT NULL,
      `sub_dtype_2` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
      `sync_ref` int(3) COLLATE utf8_unicode_ci NOT NULL,
      `options` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
      `value` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      PRIMARY KEY (`acct_ref`),
      FOREIGN KEY (`acct_ref`) REFERENCES users(`acct`),
      INDEX (`value`),
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
    What I've done so far was get the secure login script working with this setup. I'm using user levels of a(dmin), n(ormal) and d(isabled).

    For account, I don't want acct to be primary but not unique so if there's multiple users accessing the system, they can both have a value of 12345. I was also using this with admin so when I setup the searching of acct's, I can say, if NULL or has a specific value (like 1111111), then they would pull all accts, not just the assigned account.

    I also tried changing acct and attempted an alter of acct from the value above to:
    `acct` char(12) COLLATE utf8_unicode_ci NULL

    No change in response (I may need to strip it out completely first, but I wanted to see if any of you had some feedback on what I'm doing wrong).

    ACCT is the primary way that I'm organizing the data. Everything relates to ACCT.

    Any suggestions is much appreciated.
  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    i read this last post a couple of times, and i'm lost

    so...

    a primary key is unique by definition

    an auto_increment, if you're gonna use one (you don't have to, just if you want to) must be (part of) the primary key -- and 99% of the time, you want it to be the primary key all by itself (assuming, again, that you want to use an auto_increment)

    given these facts, how do you want to define your primary keys? with ACCT or with an auto_increment?

    it sounds like if you want to assign '1111111' to more than one user, then this can't be the primary key for users
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Location
    Maine
    Posts
    18
    Rep Power
    0
    Originally Posted by r937
    i read this last post a couple of times, and i'm lost

    so...

    a primary key is unique by definition

    an auto_increment, if you're gonna use one (you don't have to, just if you want to) must be (part of) the primary key -- and 99% of the time, you want it to be the primary key all by itself (assuming, again, that you want to use an auto_increment)

    given these facts, how do you want to define your primary keys? with ACCT or with an auto_increment?

    it sounds like if you want to assign '1111111' to more than one user, then this can't be the primary key for users
    From what I read, the Auto_Increment flag should work with unique. I also tested it and it's incrementing as needed for ID (maybe I just got lucky lol).

    I don't want ACCT to auto_increment. These are assigned when we register each user. Basically, it works like this:

    * Admin sets up a normal user:
    ** ID: auto_incr
    ** Username: r937
    ** Password: p@ssw0rd
    ** Name: R of the 937
    ** Email: r@937.new
    ** User Level: n
    ** Account (their actual account number): 1234567

    - Admin then sends the credentials to r@937.new so they can log in

    * r937 logs in:
    ** They can only setup data for their own account and can only view data for that account, no others. So any data that's applied by r937 adds 1234567 in the acc_ref of Table 'data' to kind of lock it down.

    As for admins, I'm trying to decide how to treat them. Maybe I should only be checking the userlevel to decide. Maybe something like:
    check_userlvl - if userlvl is a, user can access any acct and all user data - if userlvl is n, user can only access their acct (from users table) - if userlvl is d, user can not log in.

    I'm still looking into the format for php to make that work. But as far as sql, if needed, I need to be able to add additional users with the same acct number. As for admins, maybe I should allow a NULL value and just say if user is an admin and acct is NULL, allow access to any account information from the users or data tables.

    Sorry for the confusion... I thought I had it down as far as the creation of the table, but I'm obviously missing something.

    Edit: I think one of the things throwing me off is the fact that I wanted the acct in users to be the primary so I can link the acct_ref from data. Should I just skip the linking bit?
    Last edited by SiLeNCeD; December 5th, 2012 at 03:56 PM. Reason: Last minute thought
  28. #15
  29. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    Originally Posted by SiLeNCeD
    Should I just skip the linking bit?
    oh gosh no

    PRIMARY KEY and FOREIGN KEY are intended to ensure data integrity

    you should not abandon this ideal at the outset of your database career!

    if you assign unique account numbers (from somewhere), then in my opinion you make that a UNIQUE column, and use the auto_increment id as your PRIMARY KEY and use it for linking related tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo