Design question: one big field or slave table
Matthieu
I've got a design question:
Currently, we've got a table called "session" which contains all data
about a production session. I won't explain what is exactly a
"session", that's not the scope.
In this table, a field holds a "lot number", the "lot number involved
suring a session.
For now, the business needs the ability to records multiple lot numbers
per session. So there are several lot numbers for one session record.
The good way of DB design suppose to extract the "lot number field" in
a child table that would make the link between a "session" and a "lot
number" with, why not, a unique ID primary key.
This design would complicate the magangement application design: hard
to display in a grid all sessions with its lot numbers (separated by
commas for instances), performances issues (joins), grouping, cursor
processing, etc...
What are the defaults of enlarging the current "lot number" field to
let the user enter more than one data? Of course, a little treatment
would check the format string.
Thanks in advance, any help would be very appreciated
Matthieu
Bob Badour
There are well-known problems with what you are proposing. The first
hint is the dbms is a logic system and a database is a set of facts.
Does your business need to make logical inferences about lot numbers or
about arbitrary strings that may contain lot numbers?
Whether it makes sense to have multiple lot number attributes depends on
what a lot number is and why one might have multiple of them.
Anyone who pretends to have answers to your questions, while ignorant of
the myriad requirements that you have not included here, is a crank.
dawn
Hi Matthieu. I don't know, but I'm guessing you will get more
responses on comp.databases than here. There is no database "theory"
that would suggest expanding the size of the field to handle multiple
values in a single field would be a good idea when using a SQL-DBMS.
Some folks might feel your pain, however, so you might ask what people
have done in similar situations and specify which DBMS you are using.
Best wishes. --dawn
Matthieu
I'll post my query on comp.databases.
The DBMS is Oracle (9 to 10)
Bob Badour
Are you just seeking a better class of crank?
Jay Dee
I first thought, "In the modern era, wouldn't we
say "crack?" Then, remembering a number of other
posts made by this contributor, I thought, "Oh,
maybe he doesn't mean that kind of 'crank.'" Then,
thinking further, I though, "You know, maybe the
first kind of 'crank' I thought of should be
considered when trying to discern what the other
kind of 'cranks' have been posting."
That's it! Psychoactive substances! Of course!
How obvious...
[This post is no way is intended to impune any aspect
of Matthieu's character, personality, or intellectual
capability.]