Click to See Complete Forum and Search --> : Best way to break down users in SQL

12-16-2003, 04:18 PM
I've got a pretty big project coming up.

It's an online assessment that's a pre-cursor to a live class.

I need to manage multiple clients.

Each client will have multiple groups, defined by each individual live session.

The clients need to be independent of each other, but there will be a client manager who will need to be able to access each user/group for that client.

Am I better off having a generic user table with a client and a group field, or setting up a separate table for each group?

Can you create tables in SQL on the fly?

Can you place tables within tables in SQL Server?

Any help/advice/pointers would be greatly appreciated.

Kinda new to it all, sorry if my question seem noobish. Only my second ColdFusion project.

12-16-2003, 04:49 PM
er...I guess I'm asking if this is the best way or not.

12-16-2003, 06:20 PM
From a scalability standpoint it makes no sense to add tables on the fly. If you can normalize the data (which in this case can be done easily) it makes no sense to add a table for each group. Simply use a groups table that contains the groups for each client. For each user in the user table assign it to a group. Doing this will allow each client to see only their 'groups' and also allow the administrator to see all groups and all users.

One downside of this method is security. You have to make sure the tables and any calls against them are rock solid to prevent a client from seeing something they should not. The other downside would be the amount of data in the groups/users table that could impact performance, however based on the fact that you are asking this question I doubt there will be a significant amount of data in either table so using this structure to store all the clients' groups/users in a common table should not have any performance implications.

Unless it is absolutely neccessary you should never have a web based application creating permanent tables in a database, unless the application is some sort of database management system, doing so is only inviting trouble and headaches.

You can not place tables in tables, however you can place a reference to a table in another table, then could query one table to get the name of another table to run the 'real' query against.

12-16-2003, 06:42 PM
are facilatators and managers users as well? why not make them all part of the user table... so keep your user table and add one extra field of USER TYPE (manager, facilitator, etc)...

12-16-2003, 11:31 PM
I should have clarified the roles, my bad.

Facilitators are going to teach the live sessions, they work for us, not the client. (They discuss the group aggregate results in class.) They need to log on, pick a client, pick a group within that client, and they'll get some stats about that group. (Average score, how many chose 'a' for question #1, etc.)

Managers are the project managers for a particular company. They should only see their company. Each company may have more than one manager. They should be able to add/edit to the rosters for each group. (i.e. Joe Blow was scheduled for Feb 4th, and couldn't make it. Now he's going on March 12th.) They cannot see results.

This is being done because we have a client that will have approx. 2200 users next year, broken down into about 90 different sessions. However, this is a generic tool that we would like to have ready to go for other clients that may be smaller in scope. (i.e. 25 users in 1 session.) So, at any point and time, we can just add a new client, and if that client is big enough, add more than 1 group within that client.

So dgrigg, you're saying it's not so bad to lump all users into one big-ass table, and maybe we'll just create queries on the fly that select all users for a client, and all users for a group within that client?

12-17-2003, 08:48 AM
2200 user records in a table is no big issue. Just make sure you index it properly and make sure all your table definitions and queries are rock solid so that one people from one client can not see/access information for another client.

I have worked on systems with many more users/groups in common tables are never had a problem. You just have to plan and design with the fact in mind that multiple groups with seperate data are working from the same tables.

12-17-2003, 11:32 AM
Ok, thanks. The number of records in that table and how it would affect performance were my concerns.

Most queries run against that table would be to select all users from a certain company, then all users from a certain group within that company, grab their scores and spit out a CFChart.

We're doing this on a smaller scale now, about 400 users with no breakdown by company, just using Access. Guess we'll have to pony up for some SQL server space with our provider.

Are there any other tips/tricks for performance?

Should I pre-define a query for each company within the db itself, and run my CFQuery against the db's internal query?

Any tips are welcome. I'm pretty good with CFML, not so hot with SQL and database design.

Thanks for your help so far though.