Re: Sequence
From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 08/18/04
- Next message: Tom: "Re: SQL Update query"
- Previous message: Vishal Parkar: "Re: SQL Update query"
- In reply to: Mike: "Re: Sequence"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 18 Aug 2004 23:05:30 +0200
On Wed, 18 Aug 2004 10:42:59 -0700, Mike wrote:
>Hugo,
>
>I don't have any data. I understand there is no 1st,2nd or
>3rd record or so forth in relational database.
>
>I have yet to create the table, etc. I am in design stage,
>here is what I need ...
>
>I will have a table - file type is one field and file
>number is the other field,
>
>I want to be able to assign field numbers based on file
>type. These numbers should bear their own sequence
>numbers. Like I already put in my previous email,
>if the file type = 'A' the sequence will be A001,
>A002, ...
>if file type = 'B' the sequence will be B001, B002, ...
>
>Hope this makes sense.
>
>Thank you very much Hugo!
>-Mike
Hi Mike,
Allow me to disagree with you. I understand that you're in the design
stage. But your message is dealing with implementation details. It seems
that you're skipping some steps.
If yopu're in the design stage, the first thing to do is to find out what
objects you need to store information about and what attributes there are
that describe these objects. The next step is identifying functional
dependencies between attrbibutes, identifying the natural keys of your
objects and creating a normalized relational design. At this stage in the
design process, it is still forbidden to have any attributes that bear no
relation to reality. In other words: if I can look at the object and "see"
the A003 value for myself, it's okay to use it. But since you talk about
generating the A003 value, it shouldn't play any role in the design of the
conceptual model.
When the conceptual model is finished, you can carry it over to the
relational model. THIS is the right time to consider if the natural key in
the conceptual model would be the best candidate to use as primary key
(and the answer to that question is "yes" much more often than many people
believe!). If it isn't, THEN you can introduce a surrogate key. And if you
do, you should use a key that will be completely hidden and that carries
no information - it's sole purpose is to identify a particular row in a
table and that row should always be locatable as well by it's natural key.
IDENTITY is often a good candidate for a surrogate key, but (I reiterate)
only if it's used as a hidden key in a table that has a natural key as
well. I fail to see how "A003" and "B002" could be good values for a
surrogate key.
Fro the precious little details you gave so far about your situation, I
get the feeling that you are actually looking at presentation requirements
and that you are trying to put those in the table. If you try long enough,
I'm sure that you'll be able to find some kludge that will even make it
work like you request - and you have about 99% chance that you'll be very
sorry afterwards.
It's hard to get into details on your requirements, as you have disclosed
very little about the actual business problem you are trying to solve. But
if you can describe in some more detail what you're trying to do, give
some examples of the objects and attributes you want to model and some
examples of the kind of output you want to get, I can take a stab at
creating a good model for it AND the queries that will satisfy the output
you want.
Best, Hugo
-- (Remove _NO_ and _SPAM_ to get my e-mail address)
- Next message: Tom: "Re: SQL Update query"
- Previous message: Vishal Parkar: "Re: SQL Update query"
- In reply to: Mike: "Re: Sequence"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
