Making a 'sort-of' unique ID field

(I posted this in another newsgroup and it was recommended to me that I post
in here)
OK, that's an odd subject line. Let me try to explain what we have in our
table:
item language collectionID GUID
the form1 english 1 5
el form1 spanish 1 6
la form1 french 1 7
the form2 english 2 8
el form2 spanish 2 9
la form2 french 2 10
the collection ID is a way to group records into a 'set'. Now, normally, I'd
create another table called 'collections' and then relate that to this
table. So, to add a form, you'd first create a collection, then you could
add a form.
However, the developer I work with prefers this methodology of only having
one table, just to keep the number of tables and JOINS manageable (We have a
ton of tables...)
I'm trying to figure out if this is doable. The catch is making a new
collection ID and ensuring that it will be unique. I could obviously query
the DB by grabbing the top1 collectionID and then just increment it by one.
The catch is if two people are trying to add a new collection at the same
time. There's a chance that they'd both query the DB at the same time and
then attempt to make new, separate, collections, but then end up with them
sharing the same ID.
Is there a way to trigger a 'uniqueID' on a field like this on the SQL send
instead of first having to do a query look up and manually creating the
'unique' ID for this field?
Of is this just the wrong way to do it and go back to using multiple tables?
The one solution I've been given is to do the lookup, then an imediate
insert to create the new collection ID.
So, if a person clicks on NEW COLLECTION in the app, the following would
happen:
1) query the DB and request the TOP 1 collectionID
2) increment that number by 1 and then do an immediate insert of new record
into the DB
3) immediately requery the DB to grab the record with this new collectionID
and grab its GUID
4) present the form, allow the end-user to enter all the data
5) do a final UPDATE on the record with the GUID I pulled from step 3
Doable? Bad idea? Alternatives?
-DarrelWhy not store the data in two separate tables, but add a View that performs
the underlying join for you? Then you can update your individual tables,
and the other guy gets his single-table view that he likes so much... A
little something for everybody in there somewhere...
"darrel" <notreal@.hotmail.com> wrote in message
news:eBOFR1HXFHA.3320@.TK2MSFTNGP12.phx.gbl...
> (I posted this in another newsgroup and it was recommended to me that I
> post
> in here)
> OK, that's an odd subject line. Let me try to explain what we have in our
> table:
> item language collectionID GUID
> the form1 english 1 5
> el form1 spanish 1 6
> la form1 french 1 7
> the form2 english 2 8
> el form2 spanish 2 9
> la form2 french 2 10
> the collection ID is a way to group records into a 'set'. Now, normally,
> I'd
> create another table called 'collections' and then relate that to this
> table. So, to add a form, you'd first create a collection, then you could
> add a form.
> However, the developer I work with prefers this methodology of only having
> one table, just to keep the number of tables and JOINS manageable (We have
> a
> ton of tables...)
> I'm trying to figure out if this is doable. The catch is making a new
> collection ID and ensuring that it will be unique. I could obviously query
> the DB by grabbing the top1 collectionID and then just increment it by
> one.
> The catch is if two people are trying to add a new collection at the same
> time. There's a chance that they'd both query the DB at the same time and
> then attempt to make new, separate, collections, but then end up with them
> sharing the same ID.
> Is there a way to trigger a 'uniqueID' on a field like this on the SQL
> send
> instead of first having to do a query look up and manually creating the
> 'unique' ID for this field?
> Of is this just the wrong way to do it and go back to using multiple
> tables?
> The one solution I've been given is to do the lookup, then an imediate
> insert to create the new collection ID.
> So, if a person clicks on NEW COLLECTION in the app, the following would
> happen:
> 1) query the DB and request the TOP 1 collectionID
> 2) increment that number by 1 and then do an immediate insert of new
> record
> into the DB
> 3) immediately requery the DB to grab the record with this new
> collectionID
> and grab its GUID
> 4) present the form, allow the end-user to enter all the data
> 5) do a final UPDATE on the record with the GUID I pulled from step 3
> Doable? Bad idea? Alternatives?
> -Darrel
>
>
>|||> Why not store the data in two separate tables,
> but add a View that performs
> the underlying join for you?
Interesting option. Thanks!
-Darrel|||What is the entity represented by collection ID? How does it make sense
to allocate an arbitrary ID to a "collection" and then not store any
other information about it? Are you saying that a "collection" is
identifiable only by its members and not by any other information (such
as a title, user name, code or other context)? If that is truly the
case then you could always use the key of the first member assigned to
the collection and give the table a self-referencing key. Don't use
IDENTITY for a self-referencing key BTW.

> the developer I work with prefers this methodology of only having
> one table, just to keep the number of tables and JOINS manageable
That sounds ominous. Designing a relational schema on the principle of
reducing the number of joins is a recipe for poor performance and poor
integrity.
David Portas
SQL Server MVP
--|||> However, the developer I work with prefers this methodology of only having
> one table, just to keep the number of tables and JOINS manageable (We have
a
> ton of tables...)
This is where a book from one of the guys behind the relational model come
into play: INTRODUCTION TO DATABASE SYSTEMS by Date. It's a text book, so yo
u
can get an old edition for like $5.
Celko's DATA AND DATABASES is good too and has some practical info.
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis|||> What is the entity represented by collection ID? How does it make sense
> to allocate an arbitrary ID to a "collection" and then not store any
> other information about it?
Not sure what other information it needs.
The table stores a variety of forms. Each form may come in different
variations (based on language and some other things). The collectionID is
merely to tie those items together in the absence of any other unique
identifier for that group.

> Are you saying that a "collection" is
> identifiable only by its members and not by any other information (such
> as a title, user name, code or other context)?
Pretty much. We could add more information, but it's not necessarily needed.
Like I said, I'd normally do that myself. I'd make a collections table, add
the ID there, and probably a title (even if just for admin use).
But I was encouraged to avoide the extra tables if possible, and I'm just
trying to figure out if that's a practical request or not.

> If that is truly the
> case then you could always use the key of the first member assigned to
> the collection and give the table a self-referencing key. Don't use
> IDENTITY for a self-referencing key BTW.
Hmm...OK, I think I see what you are saying.
So, I'd remove collection ID and add two new fields:
item language uniqueCollectionStart CollectionID
the form1 english 1 NULL
el form1 spanish 2 1
la form1 french 3 1
Hmm...yea, that would work. The english form becomes the 'parent' of the
collection and the other's relate to it that way.
But I do think things are leaning towards making a separate table. Which I'm
fine with, personally. ;o)
-Darrel|||> Each form may come in different
> variations (based on language and some other things). The
collectionID is
> merely to tie those items together in the absence of any other unique

> identifier for that group.
I still don't see how the application or the user is supposed to know
which group is which if the only identifying information is an
artficially generated number. "I created a collection last month - I
think it was 7239. Or was it 7293...?" Usually, exposed surrogate keys
come with other information to identify them in the data model -
otherwise there is no obvious point to them.

> So, I'd remove collection ID and add two new fields:
Why add a new column? Doesn't your table have a key already?
David Portas
SQL Server MVP
--|||> I still don't see how the application or the user is supposed to know
> which group is which if the only identifying information is an
> artficially generated number.
Hmm...well, I could just query the DB and only pull up english records
which, in theory, would be unique items. But yea, I'm seeing that this is
not an ideal way to go about it.

> Why add a new column? Doesn't your table have a key already?
It does, but you had mentioned NOT using the unique identifier...though I
admit I wasn't sure why.
-Darrel|||>> However, the developer I work with prefers this methodology of only
having
one table, just to keep the number of tables and JOINS manageable (We
have a
ton of tables...) <<
First of all, never design a database for the purpose of front end
display and "easier" application coding. You design a database to be
consistent and to maintain data integrity. You do not cross tiers in a
tiered archtecture. This is more fundamental than SQL.
Is this a OTLT problem? I hope people have gotten over that design
flaw. You also keep talking about records, fields and GUIDs which have
nothing to do with a relational model.
If you are looking for a way to do translations, try this approach:
CREATE TABLE Items
(english VARCHAR(20) NOT NULL PRIMARY KEY,
spanish VARCHAR(20) NOT NULL,
french VARCHAR(20) NOT NULL);
Then add the right collations to spanish and french. Create VIEW for
each language.|||> Is this a OTLT problem?
Not really. I'm not even sure if it's a problem.
I'm by no means a DB expert, and my gut reaction is to always build two
tables when I need to do this sort of grouping. One table to create the
groups, that then relates to the table of individual records.
I wasn't sure if there was an equally viable solution using only one table.
And, from the responses, it seems like any solution is really only a hack,
and not ideal.
As such, I think I'm going to go back to the tried-and-true two-table
method. ;o)

> If you are looking for a way to do translations, try this approach:
> CREATE TABLE Items
> (english VARCHAR(20) NOT NULL PRIMARY KEY,
> spanish VARCHAR(20) NOT NULL,
> french VARCHAR(20) NOT NULL);
I think that's what David was describing. That basically makes the English
version the parent. Of course, while it's likely there'll always be an
English version, it's not guaranteed...which goes back to being perhaps
another argument to just go with two tables.
-Darrel