User Id Generation

[Vegeta69]

Hi all,

I am facing an issue on which I really appreciate some advices.
Here is the thing:

I have a table called Companies which is like this:

Id Name
55 Foo Corp
56 Ku Corp

I have an other table referencing the Users:

Id CompanyId Name
1 55 John
2 56 Marc
3 55 Michael
4 55 Terry
5 56 Dorcel

What I need is to be able to compute the Id of the Users based on the Company Id

For Example I would like to have:

CustomId
C55U1 (Company 55 User 1) John
C56U1 Marc
C55U2 Michael
C55U3 Terry
C56U2 Dorcel

(I just need the User index relative to its own company as I can get the rest using a computed column specification)

I want to do that because the users id are exposed to the users and using the Native incremental (+1) id will expose to someone the number of created user by the other companies since he last created an user.

I would like to avoid having to do a COUNT query in case several users are created in the same time and this might not be very efficient.

The only reliable solution I can think of is to create a user table for each company but I think they might be a smarter solution but my knowledge of SQL is limited. This is why I ask for your help :)

Thanks a lot.

[ck9663]

You can do this by utilizing the PARTITION BY clause of the ROW_NUMBER(). However, there's a possibility that the result could vary as new users can be added to your table and when you sort it, it'll affect the returned value.

Happy Coding!!!

~~ CK

[Jerry Winston]

There are two ways I can immediately think of doing this one: Create a computed column or add a new field.

Here's the computed column solution:

DECLARE @users TABLE(ID int, cID int,UserID as ('C'+convert(varchar,cID)+'U'+convert(varchar,ID)),username varchar(40))
DECLARE @company TABLE(ID int,companyName varchar(40))
 
INSERT INTO @company (ID,companyName) VALUES (55,'Foo Corp')
INSERT INTO @company (ID,companyName) VALUES (56,'Ku Corp')
 
 
INSERT INTO @users (ID,cID,username) VALUES(1,55,'John')
INSERT INTO @users (ID,cID,username) VALUES(2,56,'Marc')
INSERT INTO @users (ID,cID,username) VALUES(3,55,'Michael')
INSERT INTO @users (ID,cID,username) VALUES(4,55,'Terry')
INSERT INTO @users (ID,cID,username) VALUES(5,56,'Dorcel')
 
SELECT * FROM @company
SELECT * FROM @users
 

Or you can add a new field, UserID, then update the table. After the initial update your application or a TRIGGER will have to populate the new UserID field.

DECLARE @users TABLE(ID int, cID int,UserID varchar(40),username varchar(40))
DECLARE @company TABLE(ID int,companyName varchar(40))
 
INSERT INTO @company (ID,companyName) VALUES (55,'Foo Corp')
INSERT INTO @company (ID,companyName) VALUES (56,'Ku Corp')
 
 
INSERT INTO @users (ID,cID,username) VALUES(1,55,'John')
INSERT INTO @users (ID,cID,username) VALUES(2,56,'Marc')
INSERT INTO @users (ID,cID,username) VALUES(3,55,'Michael')
INSERT INTO @users (ID,cID,username) VALUES(4,55,'Terry')
INSERT INTO @users (ID,cID,username) VALUES(5,56,'Dorcel')
 
UPDATE @users
    SET UserID = 'C'+convert(varchar,cID)+'U'+convert(varchar,ID)
 
SELECT * FROM @company
SELECT * FROM @users
 

Ok, now you have a few code samples to work with to get your project back on track, but I have to warn you: although each code sample produces the same result they are not alike. The computed column solution is not ideal for indexing and searching on the UserID field without PERSISTing the computed column data. The extra data field solution is good for indexing and searching however it just adds more data to your table, data that is already exists elsewhere in the database. I'm just guessing, but I think the requirement of a UserID arises from the need to present the user with something more readable than just the number 1 or 2 as a UserID. In any case, I'd still recommend using the original ID and companyID fields for querying within your application and using the UserID for strictly presentation purposes. If you start to use the composite UserID field as the primary query field, you'll eventually be required to decompose the key to relate the sub keys to their respective tables-a computational overhead nightmare you do not want to incur when querying a 'key'.

Hope I helped. Don't forget to let us know how it turned out.

[Vegeta69]

ck9663

Thanks for you answer which may me a good start leading to the right solution.

b0010100 thanks also for your input however the problem is a little bit more complex than just formatting the Id. It is more about maintaining an identification sequence that are independent for each company users. The formating itself is not a problem.

Here are more details on the issue I am facing:
I don't want the id to be computed each time I request it as it supposed to stay always the same and therefore if we stay logical there is no need to compute a static value several times. (therefore computed column that cannot be made persistent is not a option)

Users can be deleted => therefore the RowNumber with partition will (as you mentioned) return different results in case you delete a user.

At the end all I need is something (ideally) built in, reliable (that insure uniqueness in every situation), that will give me the number of previously created users for a given company when creating a new user. Please note that because the users can be deleted a Count query cannot be used as the result may change from one time to an other.

This is why at the end the only solution (based on my basic sql knowledge) that fulfill all these requirements is to store the users into a company dedicated table and use a simple auto compute identity column (shame to create several (hundreds to thousands...) tables just to store a few users in each one...)

If someone tells me that the number of table does not actually matter and that I can have a massive number of tables in my database without impacting the performance, reliability then I might unwillingly consider the above solution.

Also every solution that include triggers is also not good as triggers are called after the query and no one can ensure that the trigger will be run (power outage ...) and I want to be 100% sure that I can rely on the Custom Id I am trying to put in place.

Thanks a lot.

Rgds

[Jerry Winston]

Vegeta69,

Because the data necessary to create your UserID is made from several fields in the user table, if the row is committed you have your key (power outage scenario). I suggest adding your general information to the user table and then use a after TRIGGER to build your composite ID(use the inserted trigger dataset to find the ID of the newly inserted row). Even if the server fails in between committing the data and executing the trigger, you can still recover the missing key with 100% accuracy based on the committed data. Just because the field is titled UserID, we don't have to think of it as a PRIMARY KEY with a NON NULL CHECK CONSTRAINT with REFERENCES to any other table. CustomerID and CompanyID fields already establishes PRIMARY KEYs and relationships between your tables. UserID is a secondary piece of information in this scenario.

However, if you still want to ensure the creation of the UserID as though it were a PRIMARY KEY, just create a sp_CreateNewUser STORED PROCEDURE. That way you can make sure everything is in place before you COMMIT.

I'd avoid making multiple tables named after companies with identical fields. Instead just create a company table where each company gets an unique ID. You can inturn use that ID to establish a foreign key relationship with a user table containing all users and a foreign key field CompanyID that establishes the users relationship to the company.

[Vegeta69]

b0010100,

Once again thanks for helping me.

However I disagree with on on this point:

"you can still recover the missing key with 100% accuracy based on the committed data"

This is wrong because as I said before the users can be deleted and therefore the state at time T may not be the same at T+1 and running a procedure may give you different Ids.

However I came out with a solution that might be acceptable for me and I would like to share it with you and maybe have some feedback on it.

First let me remind my needs:
I create some users that are affected to the company. One can retrieve other users details based on the Id that he knows.
I don't want to expose the Identity Id (primary key) as it contains some informations that should not be exposed across companies (how many users have been created by the other companies between the creation of two users in my own company). Therefore I need a numbering for each company that is reliable (should be present all the time just as the primary key), that is unique and that never changes whatever the actions on the table (for delete especially) because user searching will be based on this generated Id.

So my solution would be to create a table called "UsersCount" that would be like this:

CompanyId UserNb
55 3
56 2

I will then create a stored procedure that take as input the company Id and will increment the UserNb by one each time it is called and return the current UserNb.

Then when inserting a new user there is two different solutions:

1)do something like:
INSERT INTO Users (CompanyId,Name,CustomId) VALUE (CompanyIDValue,Name,SELECT dbo.procedureName(CompanyIdValue))

but this is not perfectly good as the stored procedure which will increment the UserNb will be called even if the inserting has failed for whatever reason. This is not perfectly good but not critical as I will only have a +1 gap.

2)This is the best solution for me:

Create a transaction scope then do the following queries:

INSERT INTO Users (CompanyId,Name) VALUE (CompanyIDValue,Name);
UPDATE Users SET CustomId = (SELECT UserNb FROM UsersCount WHERE CompanyId = CompanyIdValue) WHERE Id = SCOPE_IDENTITY();
UPDATE UsersCount SET UserNb = (SELECT UserNb FROM UsersCount WHERE CompanyId = CompanyIdValue)+1 WHERE CompanyId = CompanyIdValue

Then if something fails everything is rolled back.

Do you think that insure uniqueness ? What if two user are created at the exact same time ? (not two inserts in a row) but two users creation coming from different web client at the exact same time.

[Jerry Winston]

I'm sorry, I completely missed your requirement! You need to know how many users were assigned to a particular company from a table that contains multiple company assignments and share an IDENTITY(1,1) ID column when users can be deleted.

Your ability to report summary and aggregate information within SQL server is only as good as the information that goes in. That being said, I would advise you to not remove entries from the User table but 'deactivate' them using a BIT field userDisabled. Set the userDisabled field to 'true' (1) when you want to 'Delete' a user. Even go as far as clearing name, address, and other fields.

The UsersCount table you proposed would be identical to a stripped down User record with only ID and CompanyID remaining. Don't create count tables, use what you already have.

If you're going to perform long-range or aggregate analysis on your data, you can't throw away your data.

[Vegeta69]

@b0010100
That was a solution that came to my mind (not to delete the records but just disable them).

I was not sure if it was efficient to keep record that are not in use anymore.

I don't see how it is identical to keep several (hundreds) of empty records in a table than to keep a single UserCount record in an other table?

So assuming that I don't delete the record but just disable them. Then on inserting a new record I would do somethink like (in a transaction to make sure that the UserCount is in the newly created record):

INSERT INTO Users (Name) VALUES (NameValue)
UPDATE Users SET UserCount = (SELECT COUNT(*) FROM Users WHERE Id <= SCOPE_IDENTITY())
=> Id being an auto incremental identity

Like this I avoid all the synchronization issues (in case several record are entered at the same time) by leveraging the Identity functionality of SQL.
At the end this is the very first implementation I had ... :/ but I thought I could do better.
Please note that I don't do the count query each time I do the UserCount Field Request but I do it only once when creating the deal.


What is your point of view on this ? Do you think it is acceptable ? (not optimal for sure :) )

[Jerry Winston]

I think it will work for your purposes. However, when working with SQL server on capable hardware, a few hundred well-designed records are not typically a concern of mine.
(I'm benchmarking a few DB designs with 2 mill rows minimum per table)

You might want to take a look at a few tweaks I added to your code. I added the CompanyID field in the INSERT statement and WHERE clause to ensure the userCount is relative to the company the user is assigned to.

INSERT INTO Users (Name,CompanyID) VALUES (NameValue,CompanyIDVALUE)
UPDATE Users SET UserCount = (SELECT COUNT(*) FROM Users WHERE Id <= SCOPE_IDENTITY() AND CompanyID = CompanyIDValue) 

This code supports the scenario where all users, regardless of company, are stored in a single User table.

[Vegeta69]

@b0010100
Hi,

Hope you are fine.
Yes of course I forgot the CompanyId condition in the usercount query.

My concern was more about not keeping the deleted record but there might be only a few hundreds per company and I agree with you this is insignificant compare to the size a table can have (and will :)).

Therefore I am going to stick to the "no delete" solution. And mark this thread as solved.

Once again thanks a lot for your time ;)

Regards.

[Vegeta69]

Hi all,

Actually I think there is an even better solution:

 
   1. INSERT INTO Users (Name,CompanyID) VALUES (NameValue,CompanyIDVALUE)
   2. UPDATE Users SET UserCount = (SELECT MAX(UserCount) + 1 FROM Users WHERE CompanyID = CompanyIDValue) 
 
 

Like this I can even delete the record If I want to as long as I don't delete the last entered one.