Suppose there are four identical rows in a table, save for some unique id
which identifies each row.
For example, John Doe and his three clones live in the same home, share the
same dob, name, address, sex, car, telephone, spouse, health insurance etc.
Would you create four rows, or one row with a quantity field, holding four?
Why?
Would you treat an order of four coats, color - red, size - small, fabric -
wool, style - safari, the same way?
Why?
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm) 13  1132 
Lyle,
In both cases I assume you mean that the item in each row is actually the same
item entered four times in the table. Given that, the table is the conventional
ItemID, Quantity, Desc, Price model. So in both cases, my opinion is that you
use one row with a quantity of four. Actually I think the four rows violates the
prime directive of database design to only include any item once in the database
and refer to it with a foreign key. With four rows you have the same item
represented four times so if any attribute changes, you must go in and change
four records in the table.
Steve
PC Datasheet
"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4... Suppose there are four identical rows in a table, save for some unique id
which identifies each row.
For example, John Doe and his three clones live in the same home, share the
same dob, name, address, sex, car, telephone, spouse, health insurance etc.
Would you create four rows, or one row with a quantity field, holding four?
Why?
Would you treat an order of four coats, color - red, size - small, fabric -
wool, style - safari, the same way?
Why?
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4... Suppose there are four identical rows in a table, save for some unique id
which identifies each row.
For example, John Doe and his three clones live in the same home, share
the same dob, name, address, sex, car, telephone, spouse, health insurance
etc. Would you create four rows, or one row with a quantity field, holding
four?
Our original Human Clone Database Management System was designed on the 'one
row with a quantity field' basis. Problem was the behaviour of the clones. A
simple request like 'pass the butter please Johns' resulted in all 4 John
picking up the butter dish and handing it over the table. If Johns got mail
all 4 of him opened the envelope, read the mail aloud, wrote a reply,
together. The person getting the reply was very confused because they got
both 1 reply and 4 replies and couldn't tell whether they'd got 4 or 1, a
bit like the holy trinity but more a jonny quadrility. So then we decided to
use SSN numbers as a way of telling him apart. This didn't really help until
we took them down the Social Security office and got their Human Clone
Distinguishing Tatooist to tatoo the number onto each John forehead. Problem
is it's made it a bit hard for the spouse. Before the tatoo she could just
moan, 'Oh John', when she was faking her orgasm, now she has to have her
eyes open so she can read the SSN off his forehead, which spoils the realism
a bit. I think some of him are starting to suspect she might just be
pretending.
Would you treat an order of four coats, color - red, size - small,
fabric - wool, style - safari, the same way?
Well, I can't begin to tell you how complex our Grain of Sand Tracking
System version 1.0 was. The problem was that getting each grain engraved
with the uniquely indentifying serial number we wanted to use as Primary Key
really slowed down the production process at the dredging station. And at
the builders yard it took absolutely ages to pass the grains underneath the
specially designed nano-scanner when we were selling them. And the invoices
you can't imagine (well, you probably can!). Line Item - grain of sand,
serial no 9823-8903-8903 several million times! Then there were all the
returns, with people bringing back grains where we'd invoiced them for grain
9823-8903-8903 but had actually delivered grain 9823-8903-8902. So we
decided to change the system so we sold the sand by quantity. That was OK
for a while (just one identifier for grains of sand) but it was still
troublesome counting the grains. So then one of our top data modelling gurus
had the bright idea of selling the sand BY WEIGHT! Boy, what a breakthrough
that was.
Just my 2 pence worth based upon my real world experience.
Mikey-mo
"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4... Suppose there are four identical rows in a table, save for some unique id
which identifies each row.
For example, John Doe and his three clones live in the same home, share
the same dob, name, address, sex, car, telephone, spouse, health insurance
etc. Would you create four rows, or one row with a quantity field, holding
four? Why?
Because that's how I would expect to enter it on a form and to print it out
on paper - it's like the invoices I send out and like the till receipts I
pick up from my local supermarket. Having a table structure that mimics how
you enter and print out the data must make everything simpler.
There would have to be some special reason to consider scrapping the
quantity field and counting the number of records instead and I reckon you
can think of one for your particular situation but don't want to let us
know. It makes for a more enigmatic question coming from someone who knows
a thing or two about databases.
Would you treat an order of four coats, color - red, size - small,
fabric - wool, style - safari, the same way?
Why?
I would treat any order for four small red woollen safari coats with a
certain degree of suspicion.
Erik
In both cases, I would further consider the "problem domain" - is there any
relevance to distinguishing between the members?
If not, a quantity field might seem reasonable. But I would expect that an
additional table would still be needed to handle the accumulation of the
instances - ie, on some date within the domain an instance existed. Then
subsequently 3 clones were made. Then the daleks threatened, and a further
20 clones were commissioned. Two were faulty and scrapped. etc. (Aside: a
value should NEVER be overwritten.)
There are some cases where possibilities have such a low occurence that
handling these is not worth the cost - eg, having a field for Male or Female
biological sex based on external criteria which does not handle "Male to
1/1/03, Female from 1/2/03". ie a 1:M relationship to the individual. And
there may be a need to record "Chromosomal sex"...
I would start with two tables, and then test to see if, within the problem
domain, one could be dropped. But if so, I would expect that the quantity
itself would be irrelevant.
If, for the purposes of the problem domain, the instances were to be
distinguished, then clearly further attributes need to be defined until a
proper candidate key is established.
Clive
"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4... Suppose there are four identical rows in a table, save for some unique id
which identifies each row.
For example, John Doe and his three clones live in the same home, share
the same dob, name, address, sex, car, telephone, spouse, health insurance
etc. Would you create four rows, or one row with a quantity field, holding
four? Why?
Would you treat an order of four coats, color - red, size - small,
fabric - wool, style - safari, the same way?
Why?
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
"Eric Kant" <er**@kant.com> wrote in
news:c5**********@titan.btinternet.com: There would have to be some special reason to consider scrapping the
quantity field and counting the number of records instead and I reckon
you can think of one for your particular situation but don't want to let
us know.
No, there is no special reason. It seems to me that the use of number in
quantity is very different than the use of number in many other situations
and I wonder if this use is proper.
I would treat any order for four small red woollen safari coats with a
certain degree of suspicion.
John Birch might have said the same thing. Regardless, using a quantity field
does not allow us to identify any specific red coat.
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4... "Eric Kant" <er**@kant.com> wrote in
news:c5**********@titan.btinternet.com:
There would have to be some special reason to consider scrapping the
quantity field and counting the number of records instead and I reckon
you can think of one for your particular situation but don't want to let
us know.
No, there is no special reason. It seems to me that the use of number in
quantity is very different than the use of number in many other situations
and I wonder if this use is proper.
It's different. Integer meaning quantity is different to integer meaning
length. I think it's perfectly proper. I tried to find something in Date or
any of the other umpteen books I've got. Couldn't find anything specific, on
a quick search. Though Date has plenty of parts tables with a qty field. So
he obviously thinks it's OK.
I would treat any order for four small red woollen safari coats with a
certain degree of suspicion.
John Birch might have said the same thing. Regardless, using a quantity
field does not allow us to identify any specific red coat.
Would you need to? That's all you need to ask. Or just as pertinent, could
you?
Your clones example I can live with for now. By the time (like never) I get
asked to model a situation involving human clones, relational theory will
have been adapted enough or abandoned completely.
Your red coats example. Dunno. Anybody done any retail stock tracking work?
Are individual items assigned tracking numbers? Some electrical goods have
serial numbers. How about red coats? And even if there IS a 'natural'
primary key, e.g. model number + manufactuer + serial number are we obliged
to use it? I don't think so.
So what happens when a red coat is returned? It get's assigned a returns
number I guess. But now it has got a unique identifier. Ahh, but now it's
stored in a separate table, tblReturns, with a many to one relationship to
tblClothes.
It's a very interesting question you've raised. And I don't think the
relational model is a very good one to provide a concrete answer to it.
Mike
"CDB" <al***@delete.wave.co.nz> wrote in message
news:c5**********@news.wave.co.nz... In both cases, I would further consider the "problem domain" - is there
any relevance to distinguishing between the members?
If not, a quantity field might seem reasonable. But I would expect that
an additional table would still be needed to handle the accumulation of the
instances - ie, on some date within the domain an instance existed. Then
subsequently 3 clones were made. Then the daleks threatened, and a
further 20 clones were commissioned. Two were faulty and scrapped. etc. (Aside: a
value should NEVER be overwritten.)
In the case of our company's order entry system you can enter a single row
with qty 4 or four identical rows with qty 1. Why would you ever want to
do the latter? Because we have customers who say that's how they want it
on the invoice (they're always right after all).
So I would say go with one row as long as you are certain that an exception
to that rule won't come along later.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Lyle Fairfield <Mi************@Invalid.Com> wrote in
news:Xn*******************@130.133.1.4: Suppose there are four identical rows in a table, save for some
unique id which identifies each row.
For example, John Doe and his three clones live in the same home,
share the same dob, name, address, sex, car, telephone, spouse,
health insurance etc. Would you create four rows, or one row with
a quantity field, holding four? Why?
Would you treat an order of four coats, color - red, size - small,
fabric - wool, style - safari, the same way?
Why?
Well, it all depends on the purpose of the application.
If for your application's purposes, clones are indeed fungible, you
would use a quantity. If not, you'd have to have some way of
distinguishing them, which might mean an artificially generated key.
I have a client who deals in antiquarian musical items, and they are
currently preparing some data to put up on their website (catalogs),
and one of the problems is that they may have multiple copies of a
book, but the condition of each is not the same, and one of them
might have been formerly owned by someone famous and bears his or
her signature. They could record them with quantity, but then
there'd be no way to clearly indicate the information about the
individual copies, which in most cases changes the price -- a
shrinkwrapped copy might go for $75 while a copy with the spine
broken (but otherwise in good condition) might go for $35, while the
copy signed by Ernst Bloch might go for $250.
When the books were brand-new, they were fungible.
Once they are no longer identical, they are not, at least for the
purposes of my client.
But the answer to the question depends entirely on the application
in question.
In regard to recording data about people, there is no proper natural
key available. None. You *must* use a surrogate key. You can't use
Address, because that's not an attribute of the person entity, but a
related entity. You can't use name, since there are plenty of
duplicates in that universe of data. You can't use SSN, because
there are duplicates and it's also data that you're not really free
to ask people for. Last, you won't always know everything about the
entity the record represents. While the entity represented may be
unique, the data you have may not be sufficient to distinguish the
two records on the basis of the data you've collected alone.
That's one place where surrogate keys come in, and there is simply
no alternative when recording data about people, unless you want to
kludge your data.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
"Eric Kant" <er**@kant.com> wrote in
news:c5**********@titan.btinternet.com: Having a table structure that mimics how
you enter and print out the data must make everything simpler.
It makes creating a data entry user interface easy.
It makes just about everything else harder.
This afternoon I'm designing a subform for entering certain readings
recorded on a half-hourly basis. I could have one record per day,
with 48 fields, or I could have a table with a date field, a time
field and the reading value field. I'm doing the latter. Why?
Because it's a helluva lot easier to get information out of.
For instance, if I wanted to graph the data, with the single-record
method, I'd have to somehow decompose the data to get it into a form
that can be fed to a graph.
For instance, if I wanted to know what days the reading exceeded a
certain value, I'd have to query 48 different fields with the
single-record solution, but only one with the multi-record
structure.
Yes, it's easier to design a data entry form for the single-record
method.
But then what can you do with the data that's been entered?
Not much!
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.86... "Eric Kant" <er**@kant.com> wrote in
news:c5**********@titan.btinternet.com:
Having a table structure that mimics how
you enter and print out the data must make everything simpler.
It makes creating a data entry user interface easy.
It makes just about everything else harder.
This afternoon I'm designing a subform for entering certain readings
recorded on a half-hourly basis. I could have one record per day,
with 48 fields, or I could have a table with a date field, a time
field and the reading value field. I'm doing the latter. Why?
Because it's a helluva lot easier to get information out of.
For instance, if I wanted to graph the data, with the single-record
method, I'd have to somehow decompose the data to get it into a form
that can be fed to a graph.
For instance, if I wanted to know what days the reading exceeded a
certain value, I'd have to query 48 different fields with the
single-record solution, but only one with the multi-record
structure.
Yes, it's easier to design a data entry form for the single-record
method.
But then what can you do with the data that's been entered?
Not much!
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Whether you enter the values in a long skinny table or a short fat one, you
still enter 48 discrete values. If you placed an order for 48 bottles of
beer you would probably not expect to enter 48 line items, unless (like your
musical post) you need to distinguish each bottle.
Anyway, I can't imagine that anybody involved in the discussion thus far has
learnt anything new. Surely everyone (including the OP) can easily see that
1 row or 4 depends on the data model you choose - and data modelling is the
day-to-day work of database designers.
"Eric Kant" <er**@kant.com> wrote in
news:c5**********@sparta.btinternet.com: Anyway, I can't imagine that anybody involved in the discussion thus far
has learnt anything new. Surely everyone (including the OP) can easily
see that 1 row or 4 depends on the data model you choose - ...
Not everyone.
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
One can easily see that 1 or 4 rows does NOT depend on the data model
chosen.
The data model is not a matter of whim.
The number of rows is "part of", not "dependent upon" the data model.
The number of rows (and tables) is dependent on that subset of reality
which is of interest to the consumer of the database.
Requirements analysis determines this subset. Then, the data modellers come
in.
Clive
"Eric Kant" <er**@kant.com> wrote in message
news:c5**********@sparta.btinternet.com... "David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.86... "Eric Kant" <er**@kant.com> wrote in
news:c5**********@titan.btinternet.com:
Having a table structure that mimics how
you enter and print out the data must make everything simpler.
It makes creating a data entry user interface easy.
It makes just about everything else harder.
This afternoon I'm designing a subform for entering certain readings
recorded on a half-hourly basis. I could have one record per day,
with 48 fields, or I could have a table with a date field, a time
field and the reading value field. I'm doing the latter. Why?
Because it's a helluva lot easier to get information out of.
For instance, if I wanted to graph the data, with the single-record
method, I'd have to somehow decompose the data to get it into a form
that can be fed to a graph.
For instance, if I wanted to know what days the reading exceeded a
certain value, I'd have to query 48 different fields with the
single-record solution, but only one with the multi-record
structure.
Yes, it's easier to design a data entry form for the single-record
method.
But then what can you do with the data that's been entered?
Not much!
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Whether you enter the values in a long skinny table or a short fat one,
you still enter 48 discrete values. If you placed an order for 48 bottles of
beer you would probably not expect to enter 48 line items, unless (like
your musical post) you need to distinguish each bottle.
Anyway, I can't imagine that anybody involved in the discussion thus far
has learnt anything new. Surely everyone (including the OP) can easily see
that 1 row or 4 depends on the data model you choose - and data modelling is
the day-to-day work of database designers.
"Eric Kant" <er**@kant.com> wrote in message
news:c5**********@sparta.btinternet.com... Surely everyone (including the OP) can easily see that
1 row or 4 depends on the data model you choose
I like to choose one that matches my hair colour. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Ben Ingram |
last post by:
Hi all,
I am writing a template matrix class in which the template parameters are
the number of rows and number of columns. There are a number of reasons
why this is an appropriate tradeoff for...
|
by: M-One |
last post by:
See subject: how do I calloc (and free the memory, if that's not
free(my_bytes);) this?
TIA!
|
by: I wish |
last post by:
I found someone wrote this code
int **array2, nrows, ncolumns, i;
scanf( "%d %d", &nrows, &ncolumns );
array2 = malloc(nrows*sizeof(int *) + nrows*ncolumns*sizeof(int));
for(i = 0; i <...
|
by: Hamilton Woods |
last post by:
Diehards,
I developed a template matrix class back around 1992 using Borland C++ 4.5
(ancestor of C++ Builder) and haven't touched it until a few days ago. I
pulled it from the freezer and...
|
by: gchq |
last post by:
Hi there
Here is the situation - a table is built dynamically with values in the cells I need to retrieve and enter into a database. I have found a way of getting values using JavaScript, but of...
|
by: Paul David Buchan |
last post by:
Hello,
I'm attempting to write a program to read in database files (.dbf).
When I do it all as a single procedure in main, everything works.
However, what I really want, is to pass the database...
|
by: xiao |
last post by:
HI~ guys , I have a program here (Sorry it is very long about 240
lines.) It can read and write the header information successfully but
it cannot write the array successfully. I guess there is...
|
by: Adam Chapman |
last post by:
Hi,
Im trying to migrate from programming in Matlab over to C. Im trying
to make a simple function to multiply one matrix by the other. I've
realised that C can't determine the size of a 2d...
|
by: rijaalu |
last post by:
I am designing a matrix class that performs addition, multicpication, substraction and division. When ever i complie the code it shows an error.
include <iostream>
using namespace std;
class...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
