Page 2 of 2 First 12
  • Jump to page:
    #16
  1. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,031
    Rep Power
    4210
    oh wait...

    "I need to be able to add additional users with the same acct number"`

    sorry, acct cannot be declared UNIQUE in the users table
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  2. #17
  3. 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
    oh wait...

    "I need to be able to add additional users with the same acct number"`

    sorry, acct cannot be declared UNIQUE in the users table
    Now I'm confused lol. So do you have any suggestions? Everything is about the account. Let me give you an example of some of the data table. I'll use the same example from before with the user:

    Admin sets up a normal user
    • ID: 12 (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


    User logs in and submits the following (just an example to show how the data will flow in the data table - this will be vary variable on what they submit)

    Name of Association: DevShed United
    Address 1: 123 Test Dr.
    Address 2:
    City: Test City
    St: TE
    Zip: 12345

    Phone: 111-222-3333
    Primary Contact: Ross
    POC E-mail: ross@937.new

    Division: Technical
    Division Supervisor: Alf

    Item Category: IT
    Item 1: Supplies
    Item 2: DNI
    Item 3: Service

    So the table would look something like this after submission:
    Code:
    acct_ref  | data_type | sub_dtype_1  | sub_dtype_2  | sync_ref | options | value
    -----------------------------------------------------------------------------------------
    1234567  | assoc       | name            |                    | 001        |           | DevShed United
    1234567  | assoc       | address         | line1             | 001        |           | 123 Test Dr.
    1234567  | assoc       | address         | city              | 002        |           | Test City
    1234567  | assoc       | address         | state            | 003        |           | TE
    1234567  | assoc       | address         | zip               | 004         |          | 12345
    1234567  | assoc       | phone           | primary         | 001         |           | 111-222-3333
    1234567  | poc          | name           |                    | 001        |           | Ross
    1234567  | poc          | email            |                    | 002        |           | ross@937.new
    1234567  | div           | name          |                    | 001         |           | Technical
    1234567  | div           | supervisor   |                     | 002        |            | Alf
    1234567  | div           | cat            | name             | 001         |           | IT
    1234567  | div           | item           | name             | 001         |          | Supplies
    1234567  | div           | item           | cat               | 001         |           | IT
    1234567  | div           | item           | name             | 002        |            | DNI
    1234567  | div           | item           | cat                | 002        |            | IT
    1234567  | div           | item           | name             | 003        |            | Service
    1234567  | div           | item           | cat               | 003         |            | IT
    (sorry about the odd lengths - still getting use to how to display properly)

    Options will be used for some other data types. I know this probably looks a little weird, but I wrote it out quite a few times and it seems to give me the flexability in what I'm submitting.

    So, looking at the acct_ref field, you can see that I'm using that to really lock down all of the data. So now my question is, what's the best way to keep that constraint if I can't use that field for Foreign or Primary keys? Do I need another field in there to make this work the way I'm thinking?

    Edit: If I chose to give only one login per account, could I use the acct field as a primary key and when I create an admin use the IGNORE flag to allow the admins to not have a value (or use NULL) in the acct field?
    Last edited by SiLeNCeD; December 6th, 2012 at 10:23 AM. Reason: Quick Question
  4. #18
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,031
    Rep Power
    4210
    do me a favour, please set up these two tables, then populate the accts table with a couple of valid accounts, then populate the users table with a couple of valid users for each account, and let me know if there are any errors along the way
    Code:
    CREATE TABLE accts 
    , acct INTEGER NOT NULL PRIMARY KEY
    , descr VARCHAR(255) NOT NULL
    );
    CREATE TABLE users 
    ( id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY 
    , username VARCHAR(255) NOT NULL
    , UNIQUE KEY username (username)
    , acct INTEGER NOT NULL
    , CONSTRAINT user_acct FOREIGN KEY ( acct )
         REFERENCES accts ( acct )
         ON DELETE RESTRICT
         ON UPDATE CASCADE
    , password char(64) NOT NULL
    , name char(64) NOT NULL
    , salt char(16) NOT NULL
    , email VARCHAR(255) NOT NULL
    , userlvl char(1) NOT NULL
    );
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  6. #19
  7. 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
    do me a favour, please set up these two tables, then populate the accts table with a couple of valid accounts, then populate the users table with a couple of valid users for each account, and let me know if there are any errors along the way
    Code:
    CREATE TABLE accts 
    , acct INTEGER NOT NULL PRIMARY KEY
    , descr VARCHAR(255) NOT NULL
    );
    CREATE TABLE users 
    ( id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY 
    , username VARCHAR(255) NOT NULL
    , UNIQUE KEY username (username)
    , acct INTEGER NOT NULL
    , CONSTRAINT user_acct FOREIGN KEY ( acct )
         REFERENCES accts ( acct )
         ON DELETE RESTRICT
         ON UPDATE CASCADE
    , password char(64) NOT NULL
    , name char(64) NOT NULL
    , salt char(16) NOT NULL
    , email VARCHAR(255) NOT NULL
    , userlvl char(1) NOT NULL
    );

    On it. Thanks for bearing with me.
  8. #20
  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
    do me a favour, please set up these two tables, then populate the accts table with a couple of valid accounts, then populate the users table with a couple of valid users for each account, and let me know if there are any errors along the way
    Code:
    CREATE TABLE accts 
    , acct INTEGER NOT NULL PRIMARY KEY
    , descr VARCHAR(255) NOT NULL
    );
    CREATE TABLE users 
    ( id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY 
    , username VARCHAR(255) NOT NULL
    , UNIQUE KEY username (username)
    , acct INTEGER NOT NULL
    , CONSTRAINT user_acct FOREIGN KEY ( acct )
         REFERENCES accts ( acct )
         ON DELETE RESTRICT
         ON UPDATE CASCADE
    , password char(64) NOT NULL
    , name char(64) NOT NULL
    , salt char(16) NOT NULL
    , email VARCHAR(255) NOT NULL
    , userlvl char(1) NOT NULL
    );
    I'm still working on this, but are you coming to the conclusion of having a 3rd table to be the primary link between users and data for reference?

    acct (contains accts and admin code)

    users (FK to acct)

    data (FK to acct)
  10. #21
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Location
    Maine
    Posts
    18
    Rep Power
    0
    Took me a bit to get migrated to my new hosting. I used your suggestion for the the acct and users table. I had to make a couple of modifications. First I had to adjust the acct value from INT to BIGINT(14) as it was changing my account numbers from a 12 to 14 digit value to 2147483647. I also made this an UNSIGNED value as it will never be a negative value.

    I also had to change the ending statement of the table to add InnoDB as my default on the new hosting is MyISAM. So I ended up with this (which seems to work so far):

    Code:
    ACCTS table:
    
    CREATE TABLE accts(
        acct BIGINT(14) UNSIGNED NOT NULL PRIMARY KEY,
        descr VARCHAR(255) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    
    USERS table:
    
    CREATE TABLE users(
        id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(255) NOT NULL,
        UNIQUE KEY username (username),
        acct BIGINT(14) UNSIGNED NOT NULL,
        CONSTRAINT user_acct FOREIGN KEY (acct)
            REFERENCES accts (acct)
            ON DELETE RESTRICT
            ON UPDATE CASCADE,
        password char(64) NOT NULL,
        name char(64) NOT NULL,
        salt char(16) NOT NULL,
        email VARCHAR(255) NOT NULL,
        userlvl char(1) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    So as I mentioned in my last post, due to the amount of data and the type of setup, do you think I'll need 3 tables in all to complete what I'm attempting to build?

    Thanks
    Last edited by SiLeNCeD; December 19th, 2012 at 04:40 PM. Reason: Fixed wording
  12. #22
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,031
    Rep Power
    4210
    Originally Posted by SiLeNCeD
    I had to make a couple of modifications.
    well done

    Originally Posted by SiLeNCeD
    ... do you think I'll need 3 tables in all to complete what I'm attempting to build?
    i don't think so, but if you have any further questions, please feel free to post again
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  14. #23
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Location
    Maine
    Posts
    18
    Rep Power
    0
    Thanks again for all of your help. I think at this point, if I have any questions, it will be more about the individual php commands and other techniques for mysql, so I'll close this one out.

    Have a Great Holiday
Page 2 of 2 First 12
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo