Page 1 of 1

Company login page DB setup design question

Posted: Tue Sep 13, 2016 6:19 am
by hybris
Hi,
this is not so much a coding question but a designquestion how to make the DB:s. I couldnt find a better forum since the design forum is for advanced discussions and this is pretty basic.


Ok so here it goes:

I have a basic login system for my office and now I want to expand the system so my customers can log in as well but I do not know which is the best way to set up the system.

My current database looks like:

USERS (DB NAME)
-ID
-Level (controls who can change or access stuff in the site)
-Name
-Email
-PW
-Salt
-Emailverification
-Lastlogin

I also have a DB where I store info about customers like
CUSTOMERS (DB NAME)
- cID (company ID (Uniqe))
- Company name
- Adress, billing adress, and so on

I also want my suppliers to be able to log in (their DB is like customers db with adress info and so on).

The easy way would be to create a user with the name of the company/supplier name and let them log in with that info and steer what they have access to through the USERS->level but I really do not want a common company login but for each company to be able to create and manage their own users.

I thought of adding cID to the USER database since that is unique for each company. So everyone at my office will have cID = 1 for example and everyone from company x will have cID=5 or whichever ID their company have.

Then I can use the cID to determine which information is accessible for the users (I dont want users from company 2 to see the billing info for company 3 for example.

^^ Would this be a good solution or is there a better way? Any other ideas? How do I create the first user for a company (user info that is mailed to the company so they can log in and set up their users, shall i create a DB for temporary users where i delete login info as they set up their first user account or shall i make it as a regular user and the first user they create overwrites the user (temporary name).

Say for example i have Coca Cola as a customer and they have 3 users that should have acess to the system. Should i create a regular useraccount with username Cocacola and cID set to coca cola cID and then their first user (say Adam) overwrites the username Cocacola....

Any ideas what would be the best DB setup is very welcome.

Also every transaction a customer makes (like what he buys and so on) will be saved so it will be lots of transactions.

I was thinking of making a DB called transactions where I store the info and what cID made the transaction. Will this be a bad solution, is it better to create a DB for each company and store only their transactions in there (we have like 2000 customers so that would be lots of databases :) <- I do not think this is the way to go btw, just want to make sure.)

Big Thank You in advance!

Re: Company login page DB setup design question

Posted: Tue Sep 13, 2016 6:52 am
by Celauran
Adding a foreign key to your users table makes sense, though it sounds like you may need two; one for customers and one for suppliers. You could also make use of a polymorphic join table to handle relations between users and both customers and suppliers.
How do I create the first user for a company
That's potentially tricky as you want the users to be associated to a customer account, but don't want to divulge a list of your customers. May be better to create a company-level admin account yourself -- an account with the ability to create other accounts, all of which inherit the company ID -- and provide a contact there with a choose/reset password link to get them started.
lso every transaction a customer makes (like what he buys and so on) will be saved so it will be lots of transactions.

I was thinking of making a DB called transactions where I store the info and what cID made the transaction
I would consider going more granular and tying the transaction back to the user (assuming they are user-initiated) as you already have a relationship established between the user and the customer. If they are not user-initiated, then linking to customer ID is fine.

Re: Company login page DB setup design question

Posted: Tue Sep 13, 2016 7:16 am
by hybris
Hi Celauran,

thank You so much for Your input (now and in the past, You really made me a better coder).

I thought of adding a key to users with a single character to determine what kind of user it is like userType (I = Internal (my company), E = External (our external factories), S = Supplier, C = Customer and B = Both Supplier and Customer).

Another question regarding the User table - I have ID (auto inc) as primary index. If I think the system will hold approx 10k-20k users should I index some other field like cID or userType ^^ or will that be unessecary (I assume it will use more storagespace and more writeoperations even though that doesnt matter much since you hopefully only register a user one time...but I do have last login time stored in users..will the index(es) rewrite everytime i save last login (the only field that will be updated often) even though that particular column is not indexed?


- The idea with admin account is great and will save me much trouble. I can set it up manually for each customer with a temporary PW that they change first time they log in and then then can create new users from that. Thanks :)

- Most transactions will be performed by our seller/order who enters customer and qty into the system... maybe i should add a userID field to it just to be sure if we in the future will tie it to a webshop or something...

Thanks :)

Re: Company login page DB setup design question

Posted: Tue Sep 13, 2016 7:22 am
by Celauran
Quick and dirty rule of thumb for indexes is to create indexes for the columns you regularly use in WHERE clauses. I can see the foreign key being a good choice as generating a list of users per customer seems like something you and the customer admin users would need to do.
maybe i should add a userID field to it just to be sure if we in the future will tie it to a webshop or something...
I would wait and add it at that time, if it ever comes. I try to avoid building things "just in case" and wait until I actually need them.

Re: Company login page DB setup design question

Posted: Tue Sep 13, 2016 4:00 pm
by Christopher
Regarding the ID, I would recommend having an ID field that is an INT and a UserID filed that is their login credential. It is easier to make the relations to other tables on the INT, and it also allows you to change their UserID and not affect any of those relations. UserIDs that are email addresses are great because the are unique, but they do change occasionally.

For you overall design, I'd recommend separating it into parts to make it easier to maintain. For example:

- Every user can have the same login, profile management, password reset, etc. So make that personal part of the system common so you don't have to support duplicate sets of code to manage admins, suppliers and customers.

- I'd jump straight to a group based access system to control what users have access to instead of a level based system. Even if you just have a field in the user table that has something as simple as "user|admin|reports", that will allow you to specify what group has access to each protected page and give you the ability to customize access.

- Limit what people can see once they get somewhere with their customer or supplier ID. So if it is set then have your Models add "AND cid='{$user->cid}'" to your WHERE conditions.

Re: Company login page DB setup design question

Posted: Wed Sep 14, 2016 4:49 am
by hybris
Hi Christopher,

I'd like to thank You too for Your input now and in the past, it is much appreciated :)
Christopher wrote:Regarding the ID, I would recommend having an ID field that is an INT and a UserID filed that is their login credential. It is easier to make the relations to other tables on the INT, and it also allows you to change their UserID and not affect any of those relations. UserIDs that are email addresses are great because the are unique, but they do change occasionally.
Mu userID is INT (Auto increase, UNIQUE), For login you type your email + pw (pw has its own salt so I dont mix the email into the encryption) so it is no problem for a user to log in and change his email. For internal use in the site I use userID to reference the user.

Christopher wrote: For you overall design, I'd recommend separating it into parts to make it easier to maintain. For example:

- Every user can have the same login, profile management, password reset, etc. So make that personal part of the system common so you don't have to support duplicate sets of code to manage admins, suppliers and customers.
Yes the user management is the same for all users. Some things like cID can only be changed by me since it will be inherited from which company admin (I go with Celaurans idea here) create the user. Then in the system I control access via the cID for companies (company users) so they can only take part of information related to their company.
Christopher wrote: - I'd jump straight to a group based access system to control what users have access to instead of a level based system. Even if you just have a field in the user table that has something as simple as "user|admin|reports", that will allow you to specify what group has access to each protected page and give you the ability to customize access.

- Limit what people can see once they get somewhere with their customer or supplier ID. So if it is set then have your Models add "AND cid='{$user->cid}'" to your WHERE conditions.
I like the group based idea. The system was orginally created for only internal use but I want to extend it so external users can report directly into the system.. thats why I started with a security level system in the fist place.. Sales had the lowest (the less they know the better :D) then sales manager (that could post news), then purchasers, production, technical, QA, CEO and so on). Also all new registred users were automatically put to level 0 since you register online and I manually set the access levels upon confirming the registred users were (a member of my company)..

Ok time for me to go back to the drawing board :) One thing I learned is to set the structure BEFORE I start to code lol. Thanks again :)