Page 2 - Accurate attempt of a robust database - first timer

Dev Shed Forums Sponsor:

#16

December 5th, 2012, 06:03 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,376

Time spent in forums: 3 Months 1 Week 2 Days 7 h 35 m 43 sec
Reputation Power: 4140

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

#17

December 6th, 2012, 10:13 AM

SiLeNCeD

Registered User

Join Date: Nov 2012

Location: Maine

Posts: 18

Time spent in forums: 5 h 36 m 24 sec
Reputation Power: 0

Quote:

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

#18

December 6th, 2012, 10:35 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,376

Time spent in forums: 3 Months 1 Week 2 Days 7 h 35 m 43 sec
Reputation Power: 4140

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
);

#19

December 6th, 2012, 11:11 AM

SiLeNCeD

Registered User

Join Date: Nov 2012

Location: Maine

Posts: 18

Time spent in forums: 5 h 36 m 24 sec
Reputation Power: 0

Quote:

Originally Posted by r937

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.

#20

December 6th, 2012, 04:15 PM

SiLeNCeD

Registered User

Join Date: Nov 2012

Location: Maine

Posts: 18

Time spent in forums: 5 h 36 m 24 sec
Reputation Power: 0

Quote:

Originally Posted by r937

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)

#21

December 19th, 2012, 04:39 PM

SiLeNCeD

Registered User

Join Date: Nov 2012

Location: Maine

Posts: 18

Time spent in forums: 5 h 36 m 24 sec
Reputation 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

#22

December 20th, 2012, 11:11 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,376

Time spent in forums: 3 Months 1 Week 2 Days 7 h 35 m 43 sec
Reputation Power: 4140

Quote:

Originally Posted by SiLeNCeD

I had to make a couple of modifications.

well done

Quote:

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

#23

December 20th, 2012, 12:34 PM

SiLeNCeD

Registered User

Join Date: Nov 2012

Location: Maine

Posts: 18

Time spent in forums: 5 h 36 m 24 sec
Reputation 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 - Accurate attempt of a robust database - first timer

Developer Shed Advertisers and Affiliates