Hi every one!

I've got a design question:
Currently, we've got a table called "session" which contains all data
regarding a production session. I won't explain what is exactly a
"session", that's not the scope.
In this table, a field handles a "lot number", the "lot number"
involved
during 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 magagement 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 expanding the size of the current "lot number"
field to
let the user enter more than one data? Because this way is not
normalized. Of course, a little treatment
would check the format string.

The DBMS is Oracle, this table contains around 2 million rows.

Thanks in advance, any help would be very appreciated


Matthieu

"Matthieu" <vertonique (AT) yahoo (DOT) fr> wrote

I don't get what you mean by the word "handles" above. My understanding of
the rest of what you wrote is limited by this.

I mean by "handles" helds or contain, store.... a lot number data is
recorded into that field.

I'd like to add: what knid of limitations will I encounter if I store
multiple "lot number" into a long varchar string field? What will I not
be able to do?

Matthieu wrote:
The complexity you describe is a tradeoff with complexity to implement
and use the comma-separated string of lot numbers.

For instance, you've already identified the issue that your app must
check the format of the string, to prevent nonsense entries like
"1,2,3,banana,5". You could implement this by writing an Oracle function.

And there's the issue of how many lot numbers can you store in a varchar
(remember that numbers have varying length when represented in strings)?
Or, another way of looking at it is, how long does the varchar need to
be to store the greatest number of lot numbers? Is there a limit on the
quantity of lot numbers for a given session?

How does one delete a lot number from a string? How does one add a lot
number to an existing record? Do the lot numbers need to be stored
sorted? More custom functions you have to write.

How does one report the count of lot numbers for a given session? How
about reporting a count of sessions who have lot numbers between 10 and
15? How about reporting which sessions _don't_ have a certain lot
number? How do you predict the types of reports that need to be
accommodated by writing the correct custom functions?

These tasks are all simple to do with standard SQL queries if you store
the lot numbers in a child table, but to do it with the comma-separated
string, you have to write an ever-growing library of Oracle functions,
or else implement the same logic in your application code.

How much custom code will you write to support this design (including
source code control, documentation, maintenance, debugging, library
deployment, etc.) before it is obvious that it introduces _more_
complexity than you saved by storing the values in a child table?

Regards,
Bill K.

"Matthieu" <vertonique (AT) yahoo (DOT) fr> wrote

I'm not sure but it seems to me that any query involving
"find all the sessions that have lot # 123456 as one of the lot numbers"
will be lots harder to write if you store lot numbers as comma separated
sub-fields within a single database coulmn.

First of all, thanks a lot Bill and David for having spent time to
answer me.

You are all perfectly right, espacially for reporting features.

There are not specifiv maximum number of lot. Around 50% of the 2-3
million session rows only have one lot number (like today), then 45%
percent would contain 2 to 3 lot number. The left would hold more but I
guess that, for instance 15 lot number would be very uncommon.

How does one enter data? According to business needs, hte data entrie
should be perfomed "inline", I mean in a single line edit control, in a
list control. Idem for updating data. No delete is actually performed
(GMP app).

That's not the first reason why I'd like to store there data in one
field. Actually, the sessions data with their associated lot numbers
are present in many main and fully used screens of our app. The users
must see all the selected sessions in a list and so with the related
lot numbers list.
In order to display that kind of list, the SQL query would have to do
lots of computation.
Just for info, just to display the session list (without a child lot
number table), it takes about 6 seconds to display more than 10000 rows
and just for 2 months of job (common queries). Just imagine, that for
each row, I have to retrieve the list of related lot number, than
format them in a string!

I thought about an issue: why not keep the child table AND the big
field and manage them together (by trigger)?
Unless there is a pretty good solution to display quickly the lot
numbers list from the child table with a trick I was not aware of...

"Matthieu" <vertonique (AT) yahoo (DOT) fr> wrote


One enters the data interactively through an application program. The form
the data takes on the input screen depends on human factors, and ways in
which the people who see those screens are accustomed to viewing the data.

Designing database tables so that they conform to input screens nearly
always turns out badly. A better plan is to learn some broad design
principles for relational tables, and then apply those principles
judiciously to the problem at hand.

It does mean that the input application will have to transform data from the
way it appears on the input screen to the way it will appear in the stored
tables. That programming effort is usually worth it, in terms of deriving
benefit from the stored data.

Well you're on that point: the input application is actually not a real
problem.
The problem is to retrieve data and isplay them without spending more
than one minute on each screen.
I'm aware of good db design practices but here we've got a performance
issue. We are talking about millions of rows that each will have to
call a stored procedure to retrieve lot numbers. That won't make it.

"Matthieu" <vertonique (AT) yahoo (DOT) fr> wrote

suspect that the separate table isn't a performance hit, but by trying it
will show.
Jim

Matthieu wrote:
MySQL has a function GROUP_CONCAT() for this purpose. It's very easy to
use. For example:

SELECT s.session_description, GROUP_CONCAT(l.lot_number) AS lot_list
FROM sessions AS s JOIN lots AS l USING (session_id)
GROUP BY s.session_id;

GROUP_CONCAT() produces a BLOB column of comma-separated values by
default, and has options for changing the format, sorting the values, etc.

I don't know if Oracle has something analogous to GROUP_CONCAT(), but it
might.

I think that trigger will be a doozy! Don't forget you need to write
different triggers for INSERT and UPDATE. And reverse-direction
triggers in case someone writes to the string instead of the child table.

I thought of another case that will be laborious to write in code, but
easy if you use a child table: how to ensure that when you add a lot
number to an existing record, it isn't a duplicate of one that already
appears in the list.

Regards,
Bill K.