n-rows or one

[Lyle Fairfield]

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)

[PC Datasheet]

Re: n-rows or one

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" <MissingAddress @Invalid.Com> wrote in message
news:Xns94CAF26 2BEEC1FFDBA@130 .133.1.4...[color=blue]
> 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)[/color]

[Mike MacSween]

Re: n-rows or one

"Lyle Fairfield" <MissingAddress @Invalid.Com> wrote in message
news:Xns94CAF26 2BEEC1FFDBA@130 .133.1.4...[color=blue]
> 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[/color]
the[color=blue]
> same dob, name, address, sex, car, telephone, spouse, health insurance[/color]
etc.[color=blue]
> Would you create four rows, or one row with a quantity field, holding[/color]
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.
[color=blue]
> Would you treat an order of four coats, color - red, size - small,[/color]
fabric -[color=blue]
> wool, style - safari, the same way?[/color]

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

[Eric Kant]

Re: n-rows or one

"Lyle Fairfield" <MissingAddress @Invalid.Com> wrote in message
news:Xns94CAF26 2BEEC1FFDBA@130 .133.1.4...[color=blue]
> 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[/color]
the[color=blue]
> same dob, name, address, sex, car, telephone, spouse, health insurance[/color]
etc.[color=blue]
> Would you create four rows, or one row with a quantity field, holding[/color]
four?[color=blue]
> Why?[/color]

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.
[color=blue]
> Would you treat an order of four coats, color - red, size - small,[/color]
fabric -[color=blue]
> wool, style - safari, the same way?
> Why?[/color]

I would treat any order for four small red woollen safari coats with a
certain degree of suspicion.


Erik

[CDB]

Re: n-rows or one

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 "Chromosoma l 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" <MissingAddress @Invalid.Com> wrote in message
news:Xns94CAF26 2BEEC1FFDBA@130 .133.1.4...[color=blue]
> 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[/color]
the[color=blue]
> same dob, name, address, sex, car, telephone, spouse, health insurance[/color]
etc.[color=blue]
> Would you create four rows, or one row with a quantity field, holding[/color]
four?[color=blue]
> Why?
> Would you treat an order of four coats, color - red, size - small,[/color]
fabric -[color=blue]
> wool, style - safari, the same way?
> Why?
>
> --
> Lyle
> (for e-mail refer to http://ffdba.com/contacts.htm)[/color]

[Lyle Fairfield]

Re: n-rows or one

"Eric Kant" <erik@kant.co m> wrote in
news:c5itj6$a9d $1@titan.btinte rnet.com:
[color=blue]
> 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.[/color]

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.
[color=blue]
> I would treat any order for four small red woollen safari coats with a
> certain degree of suspicion.[/color]

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)

[Mike MacSween]

Re: n-rows or one

"Lyle Fairfield" <MissingAddress @Invalid.Com> wrote in message
news:Xns94CB446 A4C302FFDBA@130 .133.1.4...[color=blue]
> "Eric Kant" <erik@kant.co m> wrote in
> news:c5itj6$a9d $1@titan.btinte rnet.com:
>[color=green]
> > 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.[/color]
>
> 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.[/color]

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.
[color=blue]
>[color=green]
> > I would treat any order for four small red woollen safari coats with a
> > certain degree of suspicion.[/color]
>
> John Birch might have said the same thing. Regardless, using a quantity[/color]
field[color=blue]
> does not allow us to identify any specific red coat.[/color]

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

[Rick Brandt]

Re: n-rows or one

"CDB" <alpha@delete.w ave.co.nz> wrote in message
news:c5ivt7$1jj $1@news.wave.co .nz...[color=blue]
> In both cases, I would further consider the "problem domain" - is there[/color]
any[color=blue]
> relevance to distinguishing between the members?
>
> If not, a quantity field might seem reasonable. But I would expect that[/color]
an[color=blue]
> 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[/color]
further[color=blue]
> 20 clones were commissioned. Two were faulty and scrapped. etc. (Aside: a
> value should NEVER be overwritten.)[/color]

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

[David W. Fenton]

Re: n-rows or one

Lyle Fairfield <MissingAddress @Invalid.Com> wrote in
news:Xns94CAF26 2BEEC1FFDBA@130 .133.1.4:
[color=blue]
> 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?[/color]

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

[David W. Fenton]

Re: n-rows or one

"Eric Kant" <erik@kant.co m> wrote in
news:c5itj6$a9d $1@titan.btinte rnet.com:
[color=blue]
> Having a table structure that mimics how
> you enter and print out the data must make everything simpler.[/color]

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

[Eric Kant]

Re: n-rows or one

"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
news:Xns94CBA12 BEA157dfentonbw aynetinvali@24. 168.128.86...[color=blue]
> "Eric Kant" <erik@kant.co m> wrote in
> news:c5itj6$a9d $1@titan.btinte rnet.com:
>[color=green]
> > Having a table structure that mimics how
> > you enter and print out the data must make everything simpler.[/color]
>
> 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[/color]



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.

[Lyle Fairfield]

Re: n-rows or one

"Eric Kant" <erik@kant.co m> wrote in
news:c5khp5$74i $1@sparta.btint ernet.com:
[color=blue]
> 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 - ...[/color]

Not everyone.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[CDB]

Re: n-rows or one

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" <erik@kant.co m> wrote in message
news:c5khp5$74i $1@sparta.btint ernet.com...[color=blue]
> "David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
> news:Xns94CBA12 BEA157dfentonbw aynetinvali@24. 168.128.86...[color=green]
> > "Eric Kant" <erik@kant.co m> wrote in
> > news:c5itj6$a9d $1@titan.btinte rnet.com:
> >[color=darkred]
> > > Having a table structure that mimics how
> > > you enter and print out the data must make everything simpler.[/color]
> >
> > 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[/color]
>
>
>
> Whether you enter the values in a long skinny table or a short fat one,[/color]
you[color=blue]
> 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[/color]
your[color=blue]
> musical post) you need to distinguish each bottle.
>
> Anyway, I can't imagine that anybody involved in the discussion thus far[/color]
has[color=blue]
> learnt anything new. Surely everyone (including the OP) can easily see[/color]
that[color=blue]
> 1 row or 4 depends on the data model you choose - and data modelling is[/color]
the[color=blue]
> day-to-day work of database designers.
>
>[/color]

[Mike MacSween]

Re: n-rows or one

"Eric Kant" <erik@kant.co m> wrote in message
news:c5khp5$74i $1@sparta.btint ernet.com...
[color=blue]
> Surely everyone (including the OP) can easily see that
> 1 row or 4 depends on the data model you choose[/color]

I like to choose one that matches my hair colour.