Autoincrement Field for Multi-company database
-
I believe many have come up against this problem right from design phase:
I am making an app with multi-company (multi-branch) option, where all the different companies (or branches) will all be sharing the DB.
In a single-company app, we set many of the primary key columns to autoincrement. But if I do that for a multi-company app, the first record in Table3 for Company2 will show a key value of 15 and not 1.
To get round this problem, we have skirted setting columns to autoincrement and are incrementing them manually based on the MAX value in the TABLE for any given company.
We have created a numeric column COMPANYID for every TABLE to help with this.
Is there a way to link COMPANYID column and PRIMARYKEY column to autogenerate company-specific values?
-
Not trying to be a smart guy or anything... really trying to help...
In a single-company app, we set many of the primary key columns to autoincrement. But if I do that for a multi-company app, the first record in Table3 for Company2 will show a key value of 15 and not 1. My first question would be "so what"?
Why does the first row in Table3 for Company2 have to have an ID of 1?? 
It's a relational database and none of that should matter.To get round this problem, we have skirted setting columns to autoincrement and are incrementing them manually based on the MAX value in the TABLE for any given company. I'm thinking that's a really, really bad thing to do... people who use MAX to get the "NextID" are usually people who end up with dupes if it's not a unique column or rejected rows if it is. Like I said, not trying to be a smart guy about this, but have had many "exams precede the lessons" in this area.
I can probably help you with your currently defined problem, but first, I'd like to convince you that it should NOT matter what row number 1 or 15 is... it's a relational database and you shouldn't be concerned with either numerical or physical position in the database... just the relationships between rows of tables.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
I posted a reply here a few hours ago but see it's missing!!
No offence taken, Jeff, so do not feel bad at all. You have a perfectly valid point, and I am a true democrat, so feel welcome.
The first answer is: aesthetics (do not tell me it does not matter in software design). Since the companies are physically apart, (and whose users may not even know they are on a multi-company platform), it makes sense to give each company their unique series of numbers.
You will agree, from a user's point of view, it is bit unsettling when your serial numbers do not appear serially.
I'd said we use Max() function to get NextID, but that was to simpify matters. Actually, we hold each the NextID for each Comapny+Table combination in another table, so it is a bit like MS Access, where even if you delete the last three rows, the next insert will betray you.
That takes care of duplicate values, I believe. I had wanted to place Before Insert Triggers on each table to use the Max+1 approach but discarded it precisely because of the risk of duplicates.
As for bad experiences, we certainly have not had any, though everything is possible, so I'll be glad if you could paint a scenario or two.
Thanks again.
-
I don't seem to have the ability to edit quotes do I will just refer to what you say.
I am a big believer in aesthetics in code. I like to have my code look good (I call it 'tight'). So I spend a lot of time setting up code formatters so they are just right.
But what does that have to do with the 'problem' you specify? Aesthetics is not a concept that is applicable to data. Data is what it is. When you modify it to fit a style -- well, you're still modifying it outside the context of your problem domain.
As simply a challenge, there are many ways to do what you want. But in a real-world, working environment -- no way!
And no, I will NOT agree, from a user's point of view, that it is a bit unsettling when your serial numbers do not appear serially. I just opened a ticket with my in-house help desk and they gave me an "incident number" so I can track the issue. It was hundreds of values away from one I opened just yesterday. So what? I never gave it a thought until I read your post. If anyone should ask you to supply sequential values, ask them the same question you were asked here, "Why?" I'd be interested to know if they can give an answer other than just "because it looks nicer." If your customers are that anal about it, they can implement their own mapping scheme within their own database.
You've not had any bad experiences? Then why are you here?
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms -
GG,
Heh... ok about the aesthetics thing... I don't agree about it being unsettling about having "skip" numbers show up, but I'm also not in your position nor do I know your customers... some can be very picky
Is there a way to link COMPANYID column and PRIMARYKEY column to autogenerate company-specific values? Yes, there is... you can create a computed column (takes almost no database space) in the necessary tables to concatenate the CompanyID and the PrimaryKey columns you speak of either numerically (the preferred and faster method) or as a Varchar. Individual values of the PrimaryKey will still need to be created the same way you are currently generating the numbers (apparently, using a sequence table), but the computed column will take care of the "linking" you speak of. It's pretty simple to make a computed column... lookup CREATE TABLE in BOL and then look for the word "computed" on that page...
Whatever routine you have developed to increment the sequence table for each company, sure hope you're using SET @variable=pkcolumn=pkcolumn+@increment to update the sequence table to avoid dead-locks as much as possible.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
I thank all of you for your contributions.
I do not want to look like I am arguing but some of the replies here did not understand my need for unique series per company.
The app is being installed for multiple sites of a single client, eg Hilton Hotels.
They will be sharing a single central DB, each writing to/reading from the same tables as the others.
A logged-in user will only be able to read/write records whose CompanyID column matches the user's CompanyID, so it's like each company has its own virtual DB.
When a user views a list of records pertaining to his own site, I felt it would just be the right thing if the records have a logical series.
The example of a job service ticket is too far out to be of relevance here.
For the one who asked why I am here if I have not had any bad experiences, I'd like to ask to him to reread my post: I meant that I have not any with this particular issue.
-
It's just a gut feeling but your "need" is nothing more than a "feeling"... Hilton clients will understand that it's all the same company and they won't worry about ID gaps for an individual company.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Why are the users even aware of these values? How is it that they can even see them?
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms -
Good question (meaning I don't have a good answer).
I've thought about it for a few times also (doing away with showing the users the serial numbers), but every time I stopped short because a couple of other seemingly harmless changes I've done have come back to haunt after I've forgotten about it.
-
Hi Goodguy
Just a thought: If I'm working on an order-processing system, I make sure that the order number (or whatever you want to call it) which is printed on the order is a different entity to the IdOrder identity column in the orders (and order lines) tables. The user might want to start each year with order number 1...
If you need to show a 'serial number' to the user, and you want it to behave differently to your identity column (which it seems you do), then make it a different column - you can then do what you like with it without compromising the integrity of your database.
Cheers
ChrisM
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Thank you Chris. We keep a primary key column for these tables because the main text column can be modified by users.
Apparently someone long ago decided that the key series should be repeated for each unit/company and this was implemented in the entire database.
So now the app maintains separate series for each company and all the SQL statements are qualified with the tailer "AND COMPANYID = " & varCompanyID.
If they would have gone the simpler way, the tailer would not have been necessary, but changing everything now would be just too draining, so I think I'll let it pass and consider it a lesson well learnt.
-
That CompanyID makes a terrific partitioning column.
-Eddie
Eddie Wuerch
MCM: SQL -
I really have to agree with Jeff here. You are compromising the database design for a numbering scheme? I would say that is about the worst reason you could come up with. More importantly, what is going to keep it in order and without gaps? Am I assuming you plan on NEVER deleting any data? I would hate to see all this time and effort you are putting into assuring that that the data for everybody starts with a 1, then find out in 6 months all the 1's were deleted because of purging of data.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply