"tshad" <tscheider***@ftsolutions.com> wrote in message
news:eVBOpWAsFHA.908@tk2msftngp13.phx.gbl...
> What are the typical things you look for to decide whether to use a lookup
> table?
>
> I typically have Country lookup tables, State lookup tables, Airline
> lookup tables, etc.
>
> We also have a field that is a company type which would be one of 6
> values:
>                Corporation
>                Partnership
>                LLC
>                LLP
>                Sole Proprietorship
>                Non-Profit
>
> If I were going to set this up as a table I would either an identity field
> (tinyint) and use that in my Company table or use a char(2)
> (C,P,LC,LP,S,N) field.
>
> I have another field where the only values are "FEIN" or "SS".  Now every
> company would have either of these but for only 4 characters (char(4)), I
> don't bother putting this in a lookup table.
>
> I am also leaning towards not having the above company type field and just
> put the 2 character codes into the Company code directly.
>

> Just curious on what others use as their criteria.
>
> Thanks,
>
> Tom
>
>
>

"tshad" <tscheider***@ftsolutions.com> wrote in message
news:eVBOpWAsFHA.908@tk2msftngp13.phx.gbl...
> What are the typical things you look for to decide whether to use a lookup
> table?
>
> I typically have Country lookup tables, State lookup tables, Airline
> lookup tables, etc.
>
> We also have a field that is a company type which would be one of 6
> values:
>                Corporation
>                Partnership
>                LLC
>                LLP
>                Sole Proprietorship
>                Non-Profit
>
> If I were going to set this up as a table I would either an identity field
> (tinyint) and use that in my Company table or use a char(2)
> (C,P,LC,LP,S,N) field.
>
> I have another field where the only values are "FEIN" or "SS".  Now every
> company would have either of these but for only 4 characters (char(4)), I
> don't bother putting this in a lookup table.
>
> I am also leaning towards not having the above company type field and just
> put the 2 character codes into the Company code directly.
>
> Just curious on what others use as their criteria.
>
> Thanks,
>
> Tom
>
>
>

> "tshad" <tscheider***@ftsolutions.com> wrote in message
> news:eVBOpWAsFHA.908@tk2msftngp13.phx.gbl...
>> What are the typical things you look for to decide whether to use a
>> lookup table?
>>
>> I typically have Country lookup tables, State lookup tables, Airline
>> lookup tables, etc.
>>
>> We also have a field that is a company type which would be one of 6
>> values:
>>                Corporation
>>                Partnership
>>                LLC
>>                LLP
>>                Sole Proprietorship
>>                Non-Profit
>>
>> If I were going to set this up as a table I would either an identity
>> field (tinyint) and use that in my Company table or use a char(2)
>> (C,P,LC,LP,S,N) field.
>>
>> I have another field where the only values are "FEIN" or "SS".  Now every
>> company would have either of these but for only 4 characters (char(4)), I
>> don't bother putting this in a lookup table.
>>
>> I am also leaning towards not having the above company type field and
>> just put the 2 character codes into the Company code directly.
>>
>> Just curious on what others use as their criteria.
>>
>> Thanks,
>>
>> Tom
>>
>>
>>
>
>

>
> If they are static and the list is small, you may use a simple CHECK
> constraint on the column. If you are using abbreviations, make sure you
> document the expanded details with the actual constraint values in your
> data dictionary.
>
> --
> Anith
>

"tshad" <tscheider***@ftsolutions.com> wrote in message
news:ufxM2qBsFHA.3216@TK2MSFTNGP12.phx.gbl...
>
> "Anith Sen" <an***@bizdatasolutions.com> wrote in message
> news:%23YfKajAsFHA.2596@TK2MSFTNGP09.phx.gbl...
> > During the design phase, you might want to analyze whether this list is
> > time variant or not. If they are, you must use a table.
>
> What do you mean by time variant and why would you use a table if they
are?
>
> Thanks,
>
> Tom
> >
> > If they are static and the list is small, you may use a simple CHECK
> > constraint on the column. If you are using abbreviations, make sure you
> > document the expanded details with the actual constraint values in your
> > data dictionary.
> >
> > --
> > Anith
> >
>
>

>
> "tshad" <tscheider***@ftsolutions.com> wrote in message
> news:ufxM2qBsFHA.3216@TK2MSFTNGP12.phx.gbl...
> >
> > "Anith Sen" <an***@bizdatasolutions.com> wrote in message
> > news:%23YfKajAsFHA.2596@TK2MSFTNGP09.phx.gbl...
> > > During the design phase, you might want to analyze whether this list
is
> > > time variant or not. If they are, you must use a table.
> >
> > What do you mean by time variant and why would you use a table if they
> are?
> >
> > Thanks,
> >
> > Tom
> > >
> > > If they are static and the list is small, you may use a simple CHECK
> > > constraint on the column. If you are using abbreviations, make sure
you
> > > document the expanded details with the actual constraint values in
your
> > > data dictionary.
> > >
> > > --
> > > Anith
> > >
> >
> >
>
>

"tshad" wrote:

> "Brian Selzer" <br***@selzer-software.com> wrote in message
> news:%23vwfgGCsFHA.3340@TK2MSFTNGP15.phx.gbl...
> > Time variant means that the list may change over time.  If the list is
> > static, then a check constraint can be used to ensure that only correct
> > information can be stored in the database.  If the list can change, then
> the
> > list should be stored in a table and a foreign key constraint should be
> used
> > to ensure that only correct information can be stored in the database.
>
> So in my examples:
>
>                 Corporation
>                 Partnership
>                 LLC
>                 LLP
>                 Sole Proprietorship
>                 Non-Profit
>
> or
>
>         FEIN
>         SS
>
> Would it be better to use a Check constraint for these (could be entered by
> a dropdown list to make sure the values are correct)?
>
> If so, should I use the long or shorter versions of the entries "C" in place
> of Corporation, "P" for Partnership, "F" in place of FEIN or "S" in place of
> "SS"?
>
> Thanks,
>
> Tom
>
> >
> > "tshad" <tscheider***@ftsolutions.com> wrote in message
> > news:ufxM2qBsFHA.3216@TK2MSFTNGP12.phx.gbl...
> > >
> > > "Anith Sen" <an***@bizdatasolutions.com> wrote in message
> > > news:%23YfKajAsFHA.2596@TK2MSFTNGP09.phx.gbl...
> > > > During the design phase, you might want to analyze whether this list
> is
> > > > time variant or not. If they are, you must use a table.
> > >
> > > What do you mean by time variant and why would you use a table if they
> > are?
> > >
> > > Thanks,
> > >
> > > Tom
> > > >
> > > > If they are static and the list is small, you may use a simple CHECK
> > > > constraint on the column. If you are using abbreviations, make sure
> you
> > > > document the expanded details with the actual constraint values in
> your
> > > > data dictionary.
> > > >
> > > > --
> > > > Anith
> > > >
> > >
> > >
> >
> >
>
>
>

> You  can use check constraints fo constant things like  sex(mail,female),
> blood groups which never.If you fell there would be slightest change(that
may
> not be in the near future) in the data, better use master tables.
>
> Regards
> R.D
>
> "tshad" wrote:
>
> > "Brian Selzer" <br***@selzer-software.com> wrote in message
> > news:%23vwfgGCsFHA.3340@TK2MSFTNGP15.phx.gbl...
> > > Time variant means that the list may change over time.  If the list is
> > > static, then a check constraint can be used to ensure that only
correct
> > > information can be stored in the database.  If the list can change,
then
> > the
> > > list should be stored in a table and a foreign key constraint should
be
> > used
> > > to ensure that only correct information can be stored in the database.
> >
> > So in my examples:
> >
> >                 Corporation
> >                 Partnership
> >                 LLC
> >                 LLP
> >                 Sole Proprietorship
> >                 Non-Profit
> >
> > or
> >
> >         FEIN
> >         SS
> >
> > Would it be better to use a Check constraint for these (could be entered
by
> > a dropdown list to make sure the values are correct)?
> >
> > If so, should I use the long or shorter versions of the entries "C" in
place
> > of Corporation, "P" for Partnership, "F" in place of FEIN or "S" in
place of
> > "SS"?
> >
> > Thanks,
> >
> > Tom
> >
> > >
> > > "tshad" <tscheider***@ftsolutions.com> wrote in message
> > > news:ufxM2qBsFHA.3216@TK2MSFTNGP12.phx.gbl...
> > > >
> > > > "Anith Sen" <an***@bizdatasolutions.com> wrote in message
> > > > news:%23YfKajAsFHA.2596@TK2MSFTNGP09.phx.gbl...
> > > > > During the design phase, you might want to analyze whether this
list
> > is
> > > > > time variant or not. If they are, you must use a table.
> > > >
> > > > What do you mean by time variant and why would you use a table if
they
> > > are?
> > > >
> > > > Thanks,
> > > >
> > > > Tom
> > > > >
> > > > > If they are static and the list is small, you may use a simple
CHECK
> > > > > constraint on the column. If you are using abbreviations, make
sure
> > you
> > > > > document the expanded details with the actual constraint values in
> > your
> > > > > data dictionary.
> > > > >
> > > > > --
> > > > > Anith
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
> >

"--CELKO--" <jcelko***@earthlink.net> wrote in message
news:1125775676.311974.287530@o13g2000cwo.googlegroups.com...
> Please get a copy of SQL PROGRAMMING STYLE and read Chapter five on the
> design of encoding schemes, etc.  Hell, catch me off-line and I will
> mail you a FREE copy!
>
> You still do not know that rows are not records; fields are not
> columns; tables are not files; there is no sequential access in RDBMS,
> so IDENTITY cannot ever be a relational key, etc.  Until you learn what
> RDBMS is, you will keep asking questions that are based on a 1950's
> file model of data.  And peopel here will keep givign you kludges to
> get rid of you and you will only become a worse programmer.
>

>Until you learn what
> RDBMS is, you will keep asking questions that are based on a 1950's
> file model of data.  And peopel here will keep givign you kludges to
> get rid of you and you will only become a worse programmer.
>

"tshad" <t**@dslextreme.com> wrote in message
news:e5iNsNosFHA.1172@TK2MSFTNGP11.phx.gbl...
> "--CELKO--" <jcelko***@earthlink.net> wrote in message
> news:1125775676.311974.287530@o13g2000cwo.googlegroups.com...
>> Please get a copy of SQL PROGRAMMING STYLE and read Chapter five on the
>> design of encoding schemes, etc.  Hell, catch me off-line and I will
>> mail you a FREE copy!
>
> I already have it.
>
> And have read that chapter.  And I am not saying I don't agree with much
> of
> what you are saying, but I don't go along with all you are saying either.
>
>>
>> You still do not know that rows are not records; fields are not
>> columns; tables are not files; there is no sequential access in RDBMS,
>> so IDENTITY cannot ever be a relational key, etc.
>
> I did read your explanations in both the Sql Programming Style and Sql For
> Smarties books on identities and agree that there are some concerns, but
> this does not make them unusable or never valid uses for them.  You
> concern
> of gaps being left when deleting records would also be the case in your
> preferred method.  I agree that you would lose numbers if a transaction
> were
> rolled back  But unless you needed sequential number with no gaps, such as
> airline tickets where you need to account for every ticket printed, and
> only
> were concerned with unique number - I don't see a problem.
>
> You state that because you cannot put 2 identities in a table, it isn't a
> datatype.  Of course not.  It is a behavior on a datatype.  The datatype
> would be tinyint, integer, bigint etc.
>
> The numbering being based on the order it was added would also apply to
> your
> preferred method also, I believe.
>
> I am not trying to make a case for Identities here but I haven't seen any
> reason to completely disregard it either.  The fact that all the major
> engines have this in their products seems to mean that others think it is
> important.  Perhaps, this should be looked at in the next Sql specs so
> that
> all major engines do it the same way.  As you say, there are problems with
> different implementations (but this says there is a problem with
> implementations, not the idea of the Identity itself).
>
> I also agree that you can't rely on Identities as a Panacea, just as I
> would
> not disregard it altogether because people do.
>
> If you had been reading the WHOLE thread you would see that I was looking
> at
> optional ways of setting up this particular field (which I would also use
> for other fields).  The problem is that just because you can do something,
> doesn't mean you should do something (which is something you might say
> about
> Identities).
>
> I was trying to find a good way to handle my particular situation and what
> would I look at to determine whether to use a lookup table (encode the
> data)
> or not.  I even gave an example.
>
> In one case, I would only have 2 possible entries - FEIN and SS.  In the
> other, I would have 6 -
>                Corporation
>                Partnership
>                LLC
>                LLP
>                Sole Proprietorship
>                Non-Profit
>
> In the first case, I would just put either FEIN or SS into my Company
> Record
> as there would never be another type and there are only 4 characters.
>
> In the second case, there are 6 possibles (and as was mentioned, this may
> change - similar to your example with the punch card).  I could just put
> the
> data in the Company record with varChar of 20 and use a Check Constraint
> or
> use a lookup Table using a sequential number (whatever method I use to
> increment it - either the dreaded Identity - or your preferred method).
> Or
> just use the 1st 2 characters of each word.
>
> Your chapter 5 doesn't really answer this question, I don't believe,
> although it does have a lot of useful information.
>
> My question is when to use which.  What would cause you to pick one over
> the
> other?  What would be the point of having a table for FEIN/SS with only 2
> records and why would that be different from the other example?
>
> This was what I was asking.
>
> Thanks,
>
> Tom
>
>
>>Until you learn what
>> RDBMS is, you will keep asking questions that are based on a 1950's
>> file model of data.  And peopel here will keep givign you kludges to
>> get rid of you and you will only become a worse programmer.
>>
>
>
>

>
> > In the second case, there are 6 possibles (and as was mentioned, this
may
> > change - similar to your example with the punch card).  I could just put
> > the
> > data in the Company record with varChar of 20 and use a Check Constraint
> > or
> > use a lookup Table using a sequential number (whatever method I use to
> > increment it - either the dreaded Identity - or your preferred method).
> > Or
> > just use the 1st 2 characters of each word.
>
> As for the original question, I always use an integer for the key, either
an
> identity based one for something a user can edit, or a manually entered
one
> if they cannot.

>
> I very seldom will use a check constraint for values for the simple matter
> of fact that nearly every domain value has some other bit of information
> about it that is interesting to the user.  At the very least when did that
> value come into existence, and when is it disabled (and perhaps, what
> replaced it.)    I am not a big fan of the alpha based key, because things
> change.  HR becomes People Resources becomes Personell becomes HR again.
> Bleh.  Think about user names, we have tons of females in our company
> (perhaps tons was not the right word, I was talking about counts!) that
get
> hired as one login, and then they have to be changed to their new name.
> Royal pain if that has to touch multiple places to be changed.  Easy if
all
> you have to do is change one value (basically that is my goal in all
> designs, one value, one place it can be changed.)
>

>
> --
> --------------------------------------------------------------------------
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
convincing."
> (Oscar Wilde)
>
> "tshad" <t**@dslextreme.com> wrote in message
> news:e5iNsNosFHA.1172@TK2MSFTNGP11.phx.gbl...
> > "--CELKO--" <jcelko***@earthlink.net> wrote in message
> > news:1125775676.311974.287530@o13g2000cwo.googlegroups.com...
> >> Please get a copy of SQL PROGRAMMING STYLE and read Chapter five on the
> >> design of encoding schemes, etc.  Hell, catch me off-line and I will
> >> mail you a FREE copy!
> >
> > I already have it.
> >
> > And have read that chapter.  And I am not saying I don't agree with much
> > of
> > what you are saying, but I don't go along with all you are saying
either.
> >
> >>
> >> You still do not know that rows are not records; fields are not
> >> columns; tables are not files; there is no sequential access in RDBMS,
> >> so IDENTITY cannot ever be a relational key, etc.
> >
> > I did read your explanations in both the Sql Programming Style and Sql
For
> > Smarties books on identities and agree that there are some concerns, but
> > this does not make them unusable or never valid uses for them.  You
> > concern
> > of gaps being left when deleting records would also be the case in your
> > preferred method.  I agree that you would lose numbers if a transaction
> > were
> > rolled back  But unless you needed sequential number with no gaps, such
as
> > airline tickets where you need to account for every ticket printed, and
> > only
> > were concerned with unique number - I don't see a problem.
> >
> > You state that because you cannot put 2 identities in a table, it isn't
a
> > datatype.  Of course not.  It is a behavior on a datatype.  The datatype
> > would be tinyint, integer, bigint etc.
> >
> > The numbering being based on the order it was added would also apply to
> > your
> > preferred method also, I believe.
> >
> > I am not trying to make a case for Identities here but I haven't seen
any
> > reason to completely disregard it either.  The fact that all the major
> > engines have this in their products seems to mean that others think it
is
> > important.  Perhaps, this should be looked at in the next Sql specs so
> > that
> > all major engines do it the same way.  As you say, there are problems
with
> > different implementations (but this says there is a problem with
> > implementations, not the idea of the Identity itself).
> >
> > I also agree that you can't rely on Identities as a Panacea, just as I
> > would
> > not disregard it altogether because people do.
> >
> > If you had been reading the WHOLE thread you would see that I was
looking
> > at
> > optional ways of setting up this particular field (which I would also
use
> > for other fields).  The problem is that just because you can do
something,
> > doesn't mean you should do something (which is something you might say
> > about
> > Identities).
> >
> > I was trying to find a good way to handle my particular situation and
what
> > would I look at to determine whether to use a lookup table (encode the
> > data)
> > or not.  I even gave an example.
> >
> > In one case, I would only have 2 possible entries - FEIN and SS.  In the
> > other, I would have 6 -
> >                Corporation
> >                Partnership
> >                LLC
> >                LLP
> >                Sole Proprietorship
> >                Non-Profit
> >
> > In the first case, I would just put either FEIN or SS into my Company
> > Record
> > as there would never be another type and there are only 4 characters.
> >
> > In the second case, there are 6 possibles (and as was mentioned, this
may
> > change - similar to your example with the punch card).  I could just put
> > the
> > data in the Company record with varChar of 20 and use a Check Constraint
> > or
> > use a lookup Table using a sequential number (whatever method I use to
> > increment it - either the dreaded Identity - or your preferred method).
> > Or
> > just use the 1st 2 characters of each word.
> >
> > Your chapter 5 doesn't really answer this question, I don't believe,
> > although it does have a lot of useful information.
> >
> > My question is when to use which.  What would cause you to pick one over
> > the
> > other?  What would be the point of having a table for FEIN/SS with only
2
> > records and why would that be different from the other example?
> >
> > This was what I was asking.
> >
> > Thanks,
> >
> > Tom
> >
> >
> >>Until you learn what
> >> RDBMS is, you will keep asking questions that are based on a 1950's
> >> file model of data.  And peopel here will keep givign you kludges to
> >> get rid of you and you will only become a worse programmer.
> >>
> >
> >
> >
>
>

"Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message
news:eN7UaOqsFHA.260@TK2MSFTNGP11.phx.gbl...
> > rolled back  But unless you needed sequential number with no gaps, such
as
> > airline tickets where you need to account for every ticket printed, and
> > only
> > were concerned with unique number - I don't see a problem.
>
> Why would they need sequential numbers with no gaps?  Identities are only
> good for surrogate keys.  The keys that the user sees should be more than
a
> sequential value (much like the keys he often describes)
>
> > reason to completely disregard it either.  The fact that all the major
> > engines have this in their products seems to mean that others think it
is
> > important.
>
> Well, they all do cursors too, but we all know that they are evil.
>
> > In the second case, there are 6 possibles (and as was mentioned, this
may
> > change - similar to your example with the punch card).  I could just put
> > the
> > data in the Company record with varChar of 20 and use a Check Constraint
> > or
> > use a lookup Table using a sequential number (whatever method I use to
> > increment it - either the dreaded Identity - or your preferred method).
> > Or
> > just use the 1st 2 characters of each word.
>
> As for the original question, I always use an integer for the key, either
an
> identity based one for something a user can edit, or a manually entered
one
> if they cannot.
>
> I very seldom will use a check constraint for values for the simple matter
> of fact that nearly every domain value has some other bit of information
> about it that is interesting to the user.  At the very least when did that
> value come into existence, and when is it disabled (and perhaps, what
> replaced it.)    I am not a big fan of the alpha based key, because things
> change.  HR becomes People Resources becomes Personell becomes HR again.
> Bleh.  Think about user names, we have tons of females in our company
> (perhaps tons was not the right word, I was talking about counts!) that
get
> hired as one login, and then they have to be changed to their new name.
> Royal pain if that has to touch multiple places to be changed.  Easy if
all
> you have to do is change one value (basically that is my goal in all
> designs, one value, one place it can be changed.)
>
>
> --
> --------------------------------------------------------------------------

"Brian Selzer" <br***@selzer-software.com> wrote in message
news:u5QyyIrsFHA.3604@tk2msftngp13.phx.gbl...

>
> "Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message
> news:eN7UaOqsFHA.260@TK2MSFTNGP11.phx.gbl...
>> > rolled back  But unless you needed sequential number with no gaps, such
> as
>> > airline tickets where you need to account for every ticket printed, and
>> > only
>> > were concerned with unique number - I don't see a problem.
>>
>> Why would they need sequential numbers with no gaps?  Identities are only
>> good for surrogate keys.  The keys that the user sees should be more than
> a
>> sequential value (much like the keys he often describes)
>>
>> > reason to completely disregard it either.  The fact that all the major
>> > engines have this in their products seems to mean that others think it
> is
>> > important.
>>
>> Well, they all do cursors too, but we all know that they are evil.
>>
>> > In the second case, there are 6 possibles (and as was mentioned, this
> may
>> > change - similar to your example with the punch card).  I could just
>> > put
>> > the
>> > data in the Company record with varChar of 20 and use a Check
>> > Constraint
>> > or
>> > use a lookup Table using a sequential number (whatever method I use to
>> > increment it - either the dreaded Identity - or your preferred method).
>> > Or
>> > just use the 1st 2 characters of each word.
>>
>> As for the original question, I always use an integer for the key, either
> an
>> identity based one for something a user can edit, or a manually entered
> one
>> if they cannot.
>>
>> I very seldom will use a check constraint for values for the simple
>> matter
>> of fact that nearly every domain value has some other bit of information
>> about it that is interesting to the user.  At the very least when did
>> that
>> value come into existence, and when is it disabled (and perhaps, what
>> replaced it.)    I am not a big fan of the alpha based key, because
>> things
>> change.  HR becomes People Resources becomes Personell becomes HR again.
>> Bleh.  Think about user names, we have tons of females in our company
>> (perhaps tons was not the right word, I was talking about counts!) that
> get
>> hired as one login, and then they have to be changed to their new name.
>> Royal pain if that has to touch multiple places to be changed.  Easy if
> all
>> you have to do is change one value (basically that is my goal in all
>> designs, one value, one place it can be changed.)
>>
>>
>> --
>> --------------------------------------------------------------------------
> --
>> Louis Davidson - http://spaces.msn.com/members/drsql/
>> SQL Server MVP
>> "Arguments are to be avoided: they are always vulgar and often
> convincing."
>> (Oscar Wilde)
>>
>> "tshad" <t**@dslextreme.com> wrote in message
>> news:e5iNsNosFHA.1172@TK2MSFTNGP11.phx.gbl...
>> > "--CELKO--" <jcelko***@earthlink.net> wrote in message
>> > news:1125775676.311974.287530@o13g2000cwo.googlegroups.com...
>> >> Please get a copy of SQL PROGRAMMING STYLE and read Chapter five on
>> >> the
>> >> design of encoding schemes, etc.  Hell, catch me off-line and I will
>> >> mail you a FREE copy!
>> >
>> > I already have it.
>> >
>> > And have read that chapter.  And I am not saying I don't agree with
>> > much
>> > of
>> > what you are saying, but I don't go along with all you are saying
> either.
>> >
>> >>
>> >> You still do not know that rows are not records; fields are not
>> >> columns; tables are not files; there is no sequential access in RDBMS,
>> >> so IDENTITY cannot ever be a relational key, etc.
>> >
>> > I did read your explanations in both the Sql Programming Style and Sql
> For
>> > Smarties books on identities and agree that there are some concerns,
>> > but
>> > this does not make them unusable or never valid uses for them.  You
>> > concern
>> > of gaps being left when deleting records would also be the case in your
>> > preferred method.  I agree that you would lose numbers if a transaction
>> > were
>> > rolled back  But unless you needed sequential number with no gaps, such
> as
>> > airline tickets where you need to account for every ticket printed, and
>> > only
>> > were concerned with unique number - I don't see a problem.
>> >
>> > You state that because you cannot put 2 identities in a table, it isn't
> a
>> > datatype.  Of course not.  It is a behavior on a datatype.  The
>> > datatype
>> > would be tinyint, integer, bigint etc.
>> >
>> > The numbering being based on the order it was added would also apply to
>> > your
>> > preferred method also, I believe.
>> >
>> > I am not trying to make a case for Identities here but I haven't seen
> any
>> > reason to completely disregard it either.  The fact that all the major
>> > engines have this in their products seems to mean that others think it
> is
>> > important.  Perhaps, this should be looked at in the next Sql specs so
>> > that
>> > all major engines do it the same way.  As you say, there are problems
> with
>> > different implementations (but this says there is a problem with
>> > implementations, not the idea of the Identity itself).
>> >
>> > I also agree that you can't rely on Identities as a Panacea, just as I
>> > would
>> > not disregard it altogether because people do.
>> >
>> > If you had been reading the WHOLE thread you would see that I was
> looking
>> > at
>> > optional ways of setting up this particular field (which I would also
> use
>> > for other fields).  The problem is that just because you can do
> something,
>> > doesn't mean you should do something (which is something you might say
>> > about
>> > Identities).
>> >
>> > I was trying to find a good way to handle my particular situation and
> what
>> > would I look at to determine whether to use a lookup table (encode the
>> > data)
>> > or not.  I even gave an example.
>> >
>> > In one case, I would only have 2 possible entries - FEIN and SS.  In
>> > the
>> > other, I would have 6 -
>> >                Corporation
>> >                Partnership
>> >                LLC
>> >                LLP
>> >                Sole Proprietorship
>> >                Non-Profit
>> >
>> > In the first case, I would just put either FEIN or SS into my Company
>> > Record
>> > as there would never be another type and there are only 4 characters.
>> >
>> > In the second case, there are 6 possibles (and as was mentioned, this
> may
>> > change - similar to your example with the punch card).  I could just
>> > put
>> > the
>> > data in the Company record with varChar of 20 and use a Check
>> > Constraint
>> > or
>> > use a lookup Table using a sequential number (whatever method I use to
>> > increment it - either the dreaded Identity - or your preferred method).
>> > Or
>> > just use the 1st 2 characters of each word.
>> >
>> > Your chapter 5 doesn't really answer this question, I don't believe,
>> > although it does have a lot of useful information.
>> >
>> > My question is when to use which.  What would cause you to pick one
>> > over
>> > the
>> > other?  What would be the point of having a table for FEIN/SS with only
> 2
>> > records and why would that be different from the other example?
>> >
>> > This was what I was asking.
>> >
>> > Thanks,
>> >
>> > Tom
>> >
>> >
>> >>Until you learn what
>> >> RDBMS is, you will keep asking questions that are based on a 1950's
>> >> file model of data.  And peopel here will keep givign you kludges to
>> >> get rid of you and you will only become a worse programmer.
>> >>
>> >
>> >
>> >
>>
>>
>
>

"Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message
news:e8HfKNvsFHA.1220@TK2MSFTNGP10.phx.gbl...
> >> Well, they all do cursors too, but we all know that they are evil.
> >
> > Cursors are not evil.  Misuse of cursors is evil.
>
> No no, they are evil.  An evil, yet occasionally necessary, tool.  Just
> because a tool has reasonable use, doesn't make it not evil. My point in
> calling them evil (which might be a bit strong...) is that you should feel
> bad when you resort to their use.
>
> --
> --------------------------------------------------------------------------
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
convincing."
> (Oscar Wilde)
>
> "Brian Selzer" <br***@selzer-software.com> wrote in message
> news:u5QyyIrsFHA.3604@tk2msftngp13.phx.gbl...
>
> >
> > "Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message
> > news:eN7UaOqsFHA.260@TK2MSFTNGP11.phx.gbl...
> >> > rolled back  But unless you needed sequential number with no gaps,
such
> > as
> >> > airline tickets where you need to account for every ticket printed,
and
> >> > only
> >> > were concerned with unique number - I don't see a problem.
> >>
> >> Why would they need sequential numbers with no gaps?  Identities are
only
> >> good for surrogate keys.  The keys that the user sees should be more
than
> > a
> >> sequential value (much like the keys he often describes)
> >>
> >> > reason to completely disregard it either.  The fact that all the
major
> >> > engines have this in their products seems to mean that others think
it
> > is
> >> > important.
> >>
> >> Well, they all do cursors too, but we all know that they are evil.
> >>
> >> > In the second case, there are 6 possibles (and as was mentioned, this
> > may
> >> > change - similar to your example with the punch card).  I could just
> >> > put
> >> > the
> >> > data in the Company record with varChar of 20 and use a Check
> >> > Constraint
> >> > or
> >> > use a lookup Table using a sequential number (whatever method I use
to
> >> > increment it - either the dreaded Identity - or your preferred
method).
> >> > Or
> >> > just use the 1st 2 characters of each word.
> >>
> >> As for the original question, I always use an integer for the key,
either
> > an
> >> identity based one for something a user can edit, or a manually entered
> > one
> >> if they cannot.
> >>
> >> I very seldom will use a check constraint for values for the simple
> >> matter
> >> of fact that nearly every domain value has some other bit of
information
> >> about it that is interesting to the user.  At the very least when did
> >> that
> >> value come into existence, and when is it disabled (and perhaps, what
> >> replaced it.)    I am not a big fan of the alpha based key, because
> >> things
> >> change.  HR becomes People Resources becomes Personell becomes HR
again.
> >> Bleh.  Think about user names, we have tons of females in our company
> >> (perhaps tons was not the right word, I was talking about counts!) that
> > get
> >> hired as one login, and then they have to be changed to their new name.
> >> Royal pain if that has to touch multiple places to be changed.  Easy if
> > all
> >> you have to do is change one value (basically that is my goal in all
> >> designs, one value, one place it can be changed.)
> >>
> >>
> >> --
>
>> -------------------------------------------------------------------------
-
> > --
> >> Louis Davidson - http://spaces.msn.com/members/drsql/
> >> SQL Server MVP
> >> "Arguments are to be avoided: they are always vulgar and often
> > convincing."
> >> (Oscar Wilde)
> >>
> >> "tshad" <t**@dslextreme.com> wrote in message
> >> news:e5iNsNosFHA.1172@TK2MSFTNGP11.phx.gbl...
> >> > "--CELKO--" <jcelko***@earthlink.net> wrote in message
> >> > news:1125775676.311974.287530@o13g2000cwo.googlegroups.com...
> >> >> Please get a copy of SQL PROGRAMMING STYLE and read Chapter five on
> >> >> the
> >> >> design of encoding schemes, etc.  Hell, catch me off-line and I will
> >> >> mail you a FREE copy!
> >> >
> >> > I already have it.
> >> >
> >> > And have read that chapter.  And I am not saying I don't agree with
> >> > much
> >> > of
> >> > what you are saying, but I don't go along with all you are saying
> > either.
> >> >
> >> >>
> >> >> You still do not know that rows are not records; fields are not
> >> >> columns; tables are not files; there is no sequential access in
RDBMS,
> >> >> so IDENTITY cannot ever be a relational key, etc.
> >> >
> >> > I did read your explanations in both the Sql Programming Style and
Sql
> > For
> >> > Smarties books on identities and agree that there are some concerns,
> >> > but
> >> > this does not make them unusable or never valid uses for them.  You
> >> > concern
> >> > of gaps being left when deleting records would also be the case in
your
> >> > preferred method.  I agree that you would lose numbers if a
transaction
> >> > were
> >> > rolled back  But unless you needed sequential number with no gaps,
such
> > as
> >> > airline tickets where you need to account for every ticket printed,
and
> >> > only
> >> > were concerned with unique number - I don't see a problem.
> >> >
> >> > You state that because you cannot put 2 identities in a table, it
isn't
> > a
> >> > datatype.  Of course not.  It is a behavior on a datatype.  The
> >> > datatype
> >> > would be tinyint, integer, bigint etc.
> >> >
> >> > The numbering being based on the order it was added would also apply
to
> >> > your
> >> > preferred method also, I believe.
> >> >
> >> > I am not trying to make a case for Identities here but I haven't seen
> > any
> >> > reason to completely disregard it either.  The fact that all the
major
> >> > engines have this in their products seems to mean that others think
it
> > is
> >> > important.  Perhaps, this should be looked at in the next Sql specs
so
> >> > that
> >> > all major engines do it the same way.  As you say, there are problems
> > with
> >> > different implementations (but this says there is a problem with
> >> > implementations, not the idea of the Identity itself).
> >> >
> >> > I also agree that you can't rely on Identities as a Panacea, just as
I
> >> > would
> >> > not disregard it altogether because people do.
> >> >
> >> > If you had been reading the WHOLE thread you would see that I was
> > looking
> >> > at
> >> > optional ways of setting up this particular field (which I would also
> > use
> >> > for other fields).  The problem is that just because you can do
> > something,
> >> > doesn't mean you should do something (which is something you might
say
> >> > about
> >> > Identities).
> >> >
> >> > I was trying to find a good way to handle my particular situation and
> > what
> >> > would I look at to determine whether to use a lookup table (encode
the
> >> > data)
> >> > or not.  I even gave an example.
> >> >
> >> > In one case, I would only have 2 possible entries - FEIN and SS.  In
> >> > the
> >> > other, I would have 6 -
> >> >                Corporation
> >> >                Partnership
> >> >                LLC
> >> >                LLP
> >> >                Sole Proprietorship
> >> >                Non-Profit
> >> >
> >> > In the first case, I would just put either FEIN or SS into my Company
> >> > Record
> >> > as there would never be another type and there are only 4 characters.
> >> >
> >> > In the second case, there are 6 possibles (and as was mentioned, this
> > may
> >> > change - similar to your example with the punch card).  I could just
> >> > put
> >> > the
> >> > data in the Company record with varChar of 20 and use a Check
> >> > Constraint
> >> > or
> >> > use a lookup Table using a sequential number (whatever method I use
to
> >> > increment it - either the dreaded Identity - or your preferred
method).
> >> > Or
> >> > just use the 1st 2 characters of each word.
> >> >
> >> > Your chapter 5 doesn't really answer this question, I don't believe,
> >> > although it does have a lot of useful information.
> >> >
> >> > My question is when to use which.  What would cause you to pick one
> >> > over
> >> > the
> >> > other?  What would be the point of having a table for FEIN/SS with
only
> > 2
> >> > records and why would that be different from the other example?
> >> >
> >> > This was what I was asking.
> >> >
> >> > Thanks,
> >> >
> >> > Tom
> >> >
> >> >
> >> >>Until you learn what
> >> >> RDBMS is, you will keep asking questions that are based on a 1950's
> >> >> file model of data.  And peopel here will keep givign you kludges to
> >> >> get rid of you and you will only become a worse programmer.
> >> >>
> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>
>

"Brian Selzer" <br***@selzer-software.com> wrote in message
news:%2376sSbvsFHA.3568@TK2MSFTNGP15.phx.gbl...
> The problem is that people don't learn how to use them correctly, so you
> end
> up with expensive statements within the fetch loop.   People who purchase
> a
> firearm to protect their family, but don't bother to learn how to use it
> place their family in even more danger than they were in the first place.
>
> "Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message
> news:e8HfKNvsFHA.1220@TK2MSFTNGP10.phx.gbl...
>> >> Well, they all do cursors too, but we all know that they are evil.
>> >
>> > Cursors are not evil.  Misuse of cursors is evil.
>>
>> No no, they are evil.  An evil, yet occasionally necessary, tool.  Just
>> because a tool has reasonable use, doesn't make it not evil. My point in
>> calling them evil (which might be a bit strong...) is that you should
>> feel
>> bad when you resort to their use.
>>
>> --
>> --------------------------------------------------------------------------
> --
>> Louis Davidson - http://spaces.msn.com/members/drsql/
>> SQL Server MVP
>> "Arguments are to be avoided: they are always vulgar and often
> convincing."
>> (Oscar Wilde)
>>
>> "Brian Selzer" <br***@selzer-software.com> wrote in message
>> news:u5QyyIrsFHA.3604@tk2msftngp13.phx.gbl...
>>
>> >
>> > "Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message
>> > news:eN7UaOqsFHA.260@TK2MSFTNGP11.phx.gbl...
>> >> > rolled back  But unless you needed sequential number with no gaps,
> such
>> > as
>> >> > airline tickets where you need to account for every ticket printed,
> and
>> >> > only
>> >> > were concerned with unique number - I don't see a problem.
>> >>
>> >> Why would they need sequential numbers with no gaps?  Identities are
> only
>> >> good for surrogate keys.  The keys that the user sees should be more
> than
>> > a
>> >> sequential value (much like the keys he often describes)
>> >>
>> >> > reason to completely disregard it either.  The fact that all the
> major
>> >> > engines have this in their products seems to mean that others think
> it
>> > is
>> >> > important.
>> >>
>> >> Well, they all do cursors too, but we all know that they are evil.
>> >>
>> >> > In the second case, there are 6 possibles (and as was mentioned,
>> >> > this
>> > may
>> >> > change - similar to your example with the punch card).  I could just
>> >> > put
>> >> > the
>> >> > data in the Company record with varChar of 20 and use a Check
>> >> > Constraint
>> >> > or
>> >> > use a lookup Table using a sequential number (whatever method I use
> to
>> >> > increment it - either the dreaded Identity - or your preferred
> method).
>> >> > Or
>> >> > just use the 1st 2 characters of each word.
>> >>
>> >> As for the original question, I always use an integer for the key,
> either
>> > an
>> >> identity based one for something a user can edit, or a manually
>> >> entered
>> > one
>> >> if they cannot.
>> >>
>> >> I very seldom will use a check constraint for values for the simple
>> >> matter
>> >> of fact that nearly every domain value has some other bit of
> information
>> >> about it that is interesting to the user.  At the very least when did
>> >> that
>> >> value come into existence, and when is it disabled (and perhaps, what
>> >> replaced it.)    I am not a big fan of the alpha based key, because
>> >> things
>> >> change.  HR becomes People Resources becomes Personell becomes HR
> again.
>> >> Bleh.  Think about user names, we have tons of females in our company
>> >> (perhaps tons was not the right word, I was talking about counts!)
>> >> that
>> > get
>> >> hired as one login, and then they have to be changed to their new
>> >> name.
>> >> Royal pain if that has to touch multiple places to be changed.  Easy
>> >> if
>> > all
>> >> you have to do is change one value (basically that is my goal in all
>> >> designs, one value, one place it can be changed.)
>> >>
>> >>
>> >> --
>>
>>> -------------------------------------------------------------------------
> -
>> > --
>> >> Louis Davidson - http://spaces.msn.com/members/drsql/
>> >> SQL Server MVP
>> >> "Arguments are to be avoided: they are always vulgar and often
>> > convincing."
>> >> (Oscar Wilde)
>> >>
>> >> "tshad" <t**@dslextreme.com> wrote in message
>> >> news:e5iNsNosFHA.1172@TK2MSFTNGP11.phx.gbl...
>> >> > "--CELKO--" <jcelko***@earthlink.net> wrote in message
>> >> > news:1125775676.311974.287530@o13g2000cwo.googlegroups.com...
>> >> >> Please get a copy of SQL PROGRAMMING STYLE and read Chapter five on
>> >> >> the
>> >> >> design of encoding schemes, etc.  Hell, catch me off-line and I
>> >> >> will
>> >> >> mail you a FREE copy!
>> >> >
>> >> > I already have it.
>> >> >
>> >> > And have read that chapter.  And I am not saying I don't agree with
>> >> > much
>> >> > of
>> >> > what you are saying, but I don't go along with all you are saying
>> > either.
>> >> >
>> >> >>
>> >> >> You still do not know that rows are not records; fields are not
>> >> >> columns; tables are not files; there is no sequential access in
> RDBMS,
>> >> >> so IDENTITY cannot ever be a relational key, etc.
>> >> >
>> >> > I did read your explanations in both the Sql Programming Style and
> Sql
>> > For
>> >> > Smarties books on identities and agree that there are some concerns,
>> >> > but
>> >> > this does not make them unusable or never valid uses for them.  You
>> >> > concern
>> >> > of gaps being left when deleting records would also be the case in
> your
>> >> > preferred method.  I agree that you would lose numbers if a
> transaction
>> >> > were
>> >> > rolled back  But unless you needed sequential number with no gaps,
> such
>> > as
>> >> > airline tickets where you need to account for every ticket printed,
> and
>> >> > only
>> >> > were concerned with unique number - I don't see a problem.
>> >> >
>> >> > You state that because you cannot put 2 identities in a table, it
> isn't
>> > a
>> >> > datatype.  Of course not.  It is a behavior on a datatype.  The
>> >> > datatype
>> >> > would be tinyint, integer, bigint etc.
>> >> >
>> >> > The numbering being based on the order it was added would also apply
> to
>> >> > your
>> >> > preferred method also, I believe.
>> >> >
>> >> > I am not trying to make a case for Identities here but I haven't
>> >> > seen
>> > any
>> >> > reason to completely disregard it either.  The fact that all the
> major
>> >> > engines have this in their products seems to mean that others think
> it
>> > is
>> >> > important.  Perhaps, this should be looked at in the next Sql specs
> so
>> >> > that
>> >> > all major engines do it the same way.  As you say, there are
>> >> > problems
>> > with
>> >> > different implementations (but this says there is a problem with
>> >> > implementations, not the idea of the Identity itself).
>> >> >
>> >> > I also agree that you can't rely on Identities as a Panacea, just as
> I
>> >> > would
>> >> > not disregard it altogether because people do.
>> >> >
>> >> > If you had been reading the WHOLE thread you would see that I was
>> > looking
>> >> > at
>> >> > optional ways of setting up this particular field (which I would
>> >> > also
>> > use
>> >> > for other fields).  The problem is that just because you can do
>> > something,
>> >> > doesn't mean you should do something (which is something you might
> say
>> >> > about
>> >> > Identities).
>> >> >
>> >> > I was trying to find a good way to handle my particular situation
>> >> > and
>> > what
>> >> > would I look at to determine whether to use a lookup table (encode
> the
>> >> > data)
>> >> > or not.  I even gave an example.
>> >> >
>> >> > In one case, I would only have 2 possible entries - FEIN and SS.  In
>> >> > the
>> >> > other, I would have 6 -
>> >> >                Corporation
>> >> >                Partnership
>> >> >                LLC
>> >> >                LLP
>> >> >                Sole Proprietorship
>> >> >                Non-Profit
>> >> >
>> >> > In the first case, I would just put either FEIN or SS into my
>> >> > Company
>> >> > Record
>> >> > as there would never be another type and there are only 4
>> >> > characters.
>> >> >
>> >> > In the second case, there are 6 possibles (and as was mentioned,
>> >> > this
>> > may
>> >> > change - similar to your example with the punch card).  I could just
>> >> > put
>> >> > the
>> >> > data in the Company record with varChar of 20 and use a Check
>> >> > Constraint
>> >> > or
>> >> > use a lookup Table using a sequential number (whatever method I use
> to
>> >> > increment it - either the dreaded Identity - or your preferred
> method).
>> >> > Or
>> >> > just use the 1st 2 characters of each word.
>> >> >
>> >> > Your chapter 5 doesn't really answer this question, I don't believe,
>> >> > although it does have a lot of useful information.
>> >> >
>> >> > My question is when to use which.  What would cause you to pick one
>> >> > over
>> >> > the
>> >> > other?  What would be the point of having a table for FEIN/SS with
> only
>> > 2
>> >> > records and why would that be different from the other example?
>> >> >
>> >> > This was what I was asking.
>> >> >
>> >> > Thanks,
>> >> >
>> >> > Tom
>> >> >
>> >> >
>> >> >>Until you learn what
>> >> >> RDBMS is, you will keep asking questions that are based on a 1950's
>> >> >> file model of data.  And peopel here will keep givign you kludges
>> >> >> to
>> >> >> get rid of you and you will only become a worse programmer.
>> >> >>
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

>
> --
> ----------------------------------------------------------------------------
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing." (Oscar Wilde)
>
> "Brian Selzer" <br***@selzer-software.com> wrote in message
> news:u5QyyIrsFHA.3604@tk2msftngp13.phx.gbl...
>
>>
>> "Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message
>> news:eN7UaOqsFHA.260@TK2MSFTNGP11.phx.gbl...
>>> > rolled back  But unless you needed sequential number with no gaps,
>>> > such
>> as
>>> > airline tickets where you need to account for every ticket printed,
>>> > and
>>> > only
>>> > were concerned with unique number - I don't see a problem.
>>>
>>> Why would they need sequential numbers with no gaps?  Identities are
>>> only
>>> good for surrogate keys.  The keys that the user sees should be more
>>> than
>> a
>>> sequential value (much like the keys he often describes)
>>>
>>> > reason to completely disregard it either.  The fact that all the major
>>> > engines have this in their products seems to mean that others think it
>> is
>>> > important.
>>>
>>> Well, they all do cursors too, but we all know that they are evil.
>>>
>>> > In the second case, there are 6 possibles (and as was mentioned, this
>> may
>>> > change - similar to your example with the punch card).  I could just
>>> > put
>>> > the
>>> > data in the Company record with varChar of 20 and use a Check
>>> > Constraint
>>> > or
>>> > use a lookup Table using a sequential number (whatever method I use to
>>> > increment it - either the dreaded Identity - or your preferred
>>> > method).
>>> > Or
>>> > just use the 1st 2 characters of each word.
>>>
>>> As for the original question, I always use an integer for the key,
>>> either
>> an
>>> identity based one for something a user can edit, or a manually entered
>> one
>>> if they cannot.
>>>
>>> I very seldom will use a check constraint for values for the simple
>>> matter
>>> of fact that nearly every domain value has some other bit of information
>>> about it that is interesting to the user.  At the very least when did
>>> that
>>> value come into existence, and when is it disabled (and perhaps, what
>>> replaced it.)    I am not a big fan of the alpha based key, because
>>> things
>>> change.  HR becomes People Resources becomes Personell becomes HR again.
>>> Bleh.  Think about user names, we have tons of females in our company
>>> (perhaps tons was not the right word, I was talking about counts!) that
>> get
>>> hired as one login, and then they have to be changed to their new name.
>>> Royal pain if that has to touch multiple places to be changed.  Easy if
>> all
>>> you have to do is change one value (basically that is my goal in all
>>> designs, one value, one place it can be changed.)
>>>
>>>
>>> --
>>> --------------------------------------------------------------------------
>> --
>>> Louis Davidson - http://spaces.msn.com/members/drsql/
>>> SQL Server MVP
>>> "Arguments are to be avoided: they are always vulgar and often
>> convincing."
>>> (Oscar Wilde)
>>>
>>> "tshad" <t**@dslextreme.com> wrote in message
>>> news:e5iNsNosFHA.1172@TK2MSFTNGP11.phx.gbl...
>>> > "--CELKO--" <jcelko***@earthlink.net> wrote in message
>>> > news:1125775676.311974.287530@o13g2000cwo.googlegroups.com...
>>> >> Please get a copy of SQL PROGRAMMING STYLE and read Chapter five on
>>> >> the
>>> >> design of encoding schemes, etc.  Hell, catch me off-line and I will
>>> >> mail you a FREE copy!
>>> >
>>> > I already have it.
>>> >
>>> > And have read that chapter.  And I am not saying I don't agree with
>>> > much
>>> > of
>>> > what you are saying, but I don't go along with all you are saying
>> either.
>>> >
>>> >>
>>> >> You still do not know that rows are not records; fields are not
>>> >> columns; tables are not files; there is no sequential access in
>>> >> RDBMS,
>>> >> so IDENTITY cannot ever be a relational key, etc.
>>> >
>>> > I did read your explanations in both the Sql Programming Style and Sql
>> For
>>> > Smarties books on identities and agree that there are some concerns,
>>> > but
>>> > this does not make them unusable or never valid uses for them.  You
>>> > concern
>>> > of gaps being left when deleting records would also be the case in
>>> > your
>>> > preferred method.  I agree that you would lose numbers if a
>>> > transaction
>>> > were
>>> > rolled back  But unless you needed sequential number with no gaps,
>>> > such
>> as
>>> > airline tickets where you need to account for every ticket printed,
>>> > and
>>> > only
>>> > were concerned with unique number - I don't see a problem.
>>> >
>>> > You state that because you cannot put 2 identities in a table, it
>>> > isn't
>> a
>>> > datatype.  Of course not.  It is a behavior on a datatype.  The
>>> > datatype
>>> > would be tinyint, integer, bigint etc.
>>> >
>>> > The numbering being based on the order it was added would also apply
>>> > to
>>> > your
>>> > preferred method also, I believe.
>>> >
>>> > I am not trying to make a case for Identities here but I haven't seen
>> any
>>> > reason to completely disregard it either.  The fact that all the major
>>> > engines have this in their products seems to mean that others think it
>> is
>>> > important.  Perhaps, this should be looked at in the next Sql specs so
>>> > that
>>> > all major engines do it the same way.  As you say, there are problems
>> with
>>> > different implementations (but this says there is a problem with
>>> > implementations, not the idea of the Identity itself).
>>> >
>>> > I also agree that you can't rely on Identities as a Panacea, just as I
>>> > would
>>> > not disregard it altogether because people do.
>>> >
>>> > If you had been reading the WHOLE thread you would see that I was
>> looking
>>> > at
>>> > optional ways of setting up this particular field (which I would also
>> use
>>> > for other fields).  The problem is that just because you can do
>> something,
>>> > doesn't mean you should do something (which is something you might say
>>> > about
>>> > Identities).
>>> >
>>> > I was trying to find a good way to handle my particular situation and
>> what
>>> > would I look at to determine whether to use a lookup table (encode the
>>> > data)
>>> > or not.  I even gave an example.
>>> >
>>> > In one case, I would only have 2 possible entries - FEIN and SS.  In
>>> > the
>>> > other, I would have 6 -
>>> >                Corporation
>>> >                Partnership
>>> >                LLC
>>> >                LLP
>>> >                Sole Proprietorship
>>> >                Non-Profit
>>> >
>>> > In the first case, I would just put either FEIN or SS into my Company
>>> > Record
>>> > as there would never be another type and there are only 4 characters.
>>> >
>>> > In the second case, there are 6 possibles (and as was mentioned, this
>> may
>>> > change - similar to your example with the punch card).  I could just
>>> > put
>>> > the
>>> > data in the Company record with varChar of 20 and use a Check
>>> > Constraint
>>> > or
>>> > use a lookup Table using a sequential number (whatever method I use to
>>> > increment it - either the dreaded Identity - or your preferred
>>> > method).
>>> > Or
>>> > just use the 1st 2 characters of each word.
>>> >
>>> > Your chapter 5 doesn't really answer this question, I don't believe,
>>> > although it does have a lot of useful information.
>>> >
>>> > My question is when to use which.  What would cause you to pick one
>>> > over
>>> > the
>>> > other?  What would be the point of having a table for FEIN/SS with
>>> > only
>> 2
>>> > records and why would that be different from the other example?
>>> >
>>> > This was what I was asking.
>>> >
>>> > Thanks,
>>> >
>>> > Tom
>>> >
>>> >
>>> >>Until you learn what
>>> >> RDBMS is, you will keep asking questions that are based on a 1950's
>>> >> file model of data.  And peopel here will keep givign you kludges to
>>> >> get rid of you and you will only become a worse programmer.
>>> >>
>>> >
>>> >
>>> >
>>>
>>>
>>
>>
>
>

"tshad" <t**@dslextreme.com> wrote in message
news:#vA4e$EsFHA.3240@TK2MSFTNGP12.phx.gbl...
> "Brian Selzer" <br***@selzer-software.com> wrote in message
> news:%23vwfgGCsFHA.3340@TK2MSFTNGP15.phx.gbl...
> > Time variant means that the list may change over time.  If the list is
> > static, then a check constraint can be used to ensure that only correct
> > information can be stored in the database.  If the list can change, then
> the
> > list should be stored in a table and a foreign key constraint should be
> used
> > to ensure that only correct information can be stored in the database.
>
> So in my examples:
>
>                 Corporation
>                 Partnership
>                 LLC
>                 LLP
>                 Sole Proprietorship
>                 Non-Profit
>
> or
>
>         FEIN
>         SS
>
> Would it be better to use a Check constraint for these (could be entered
by
> a dropdown list to make sure the values are correct)?
>
> If so, should I use the long or shorter versions of the entries "C" in
place
> of Corporation, "P" for Partnership, "F" in place of FEIN or "S" in place
of
> "SS"?
>
> Thanks,
>
> Tom
>
> >
> > "tshad" <tscheider***@ftsolutions.com> wrote in message
> > news:ufxM2qBsFHA.3216@TK2MSFTNGP12.phx.gbl...
> > >
> > > "Anith Sen" <an***@bizdatasolutions.com> wrote in message
> > > news:%23YfKajAsFHA.2596@TK2MSFTNGP09.phx.gbl...
> > > > During the design phase, you might want to analyze whether this list
> is
> > > > time variant or not. If they are, you must use a table.
> > >
> > > What do you mean by time variant and why would you use a table if they
> > are?
> > >
> > > Thanks,
> > >
> > > Tom
> > > >
> > > > If they are static and the list is small, you may use a simple CHECK
> > > > constraint on the column. If you are using abbreviations, make sure
> you
> > > > document the expanded details with the actual constraint values in
> your
> > > > data dictionary.
> > > >
> > > > --
> > > > Anith
> > > >
> > >
> > >
> >
> >
>
>

"Brian Selzer" <br***@selzer-software.com> wrote in message
news:OUe5iFJsFHA.1168@TK2MSFTNGP10.phx.gbl...
> It depends.  If the list is long, then you may want to put it in a
separate
> table to make it easier to populate those drop downs.  If you're
considering
> globalizing your application, then you should definitely separate the list
> into another table, so that you can have translated lists for each
supported
> language.   I prefer to use the longer version when I use a check
> constraint, because it's less work for the application.  If there's 2
> billion rows, however, then performance and disk space become a factor,
and
> the use of a lookup table or abbreviations becomes more attractive.

>
> "tshad" <t**@dslextreme.com> wrote in message
> news:#vA4e$EsFHA.3240@TK2MSFTNGP12.phx.gbl...
> > "Brian Selzer" <br***@selzer-software.com> wrote in message
> > news:%23vwfgGCsFHA.3340@TK2MSFTNGP15.phx.gbl...
> > > Time variant means that the list may change over time.  If the list is
> > > static, then a check constraint can be used to ensure that only
correct
> > > information can be stored in the database.  If the list can change,
then
> > the
> > > list should be stored in a table and a foreign key constraint should
be
> > used
> > > to ensure that only correct information can be stored in the database.
> >
> > So in my examples:
> >
> >                 Corporation
> >                 Partnership
> >                 LLC
> >                 LLP
> >                 Sole Proprietorship
> >                 Non-Profit
> >
> > or
> >
> >         FEIN
> >         SS
> >
> > Would it be better to use a Check constraint for these (could be entered
> by
> > a dropdown list to make sure the values are correct)?
> >
> > If so, should I use the long or shorter versions of the entries "C" in
> place
> > of Corporation, "P" for Partnership, "F" in place of FEIN or "S" in
place
> of
> > "SS"?
> >
> > Thanks,
> >
> > Tom
> >
> > >
> > > "tshad" <tscheider***@ftsolutions.com> wrote in message
> > > news:ufxM2qBsFHA.3216@TK2MSFTNGP12.phx.gbl...
> > > >
> > > > "Anith Sen" <an***@bizdatasolutions.com> wrote in message
> > > > news:%23YfKajAsFHA.2596@TK2MSFTNGP09.phx.gbl...
> > > > > During the design phase, you might want to analyze whether this
list
> > is
> > > > > time variant or not. If they are, you must use a table.
> > > >
> > > > What do you mean by time variant and why would you use a table if
they
> > > are?
> > > >
> > > > Thanks,
> > > >
> > > > Tom
> > > > >
> > > > > If they are static and the list is small, you may use a simple
CHECK
> > > > > constraint on the column. If you are using abbreviations, make
sure
> > you
> > > > > document the expanded details with the actual constraint values in
> > your
> > > > > data dictionary.
> > > > >
> > > > > --
> > > > > Anith
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>