Hi all,

I am designing a database for a social science project I'm doing. It
is based on a Linnaen taxonomy (family, subfamily, etc). I know to
get it up to 3rd normal form, I will have to have separate tables for
each level in the hierarchy, with UIDs in each:

tblFamily
UID Name
1 Fam1
2 Fam2

tblSubfamily
UID Name DominatedBy
1 SubFam1 FamUID_1
2 SubFam2 FamUID_1
3 SubFam3 FamUID_2

tblBranch
UID Name DominatedBy
1 Branch1 SubFamUID_3

.... and so on.

As I understand things (having never taken a formal database design
course), I can keep it in 3rd normal form by moving down the levels
and only keeping the DominatedBy field on an "immediate ancestor"/
nontransitive basis. And there is actually not all that much other
attribute data until I get to the bottom level.

My problem is that as I move down through the levels, the
classifications become more and more controversial, as they are wont
to do. And while I am going to assign things on the bottom a certain
way, I am also going to release this database to others for them to
change as they see fit. So, in a way, I need to build in a 4th form
capacity, because things on the bottom may have a many-to-many
relationship by the time somebody else gets done with it.

So my question is, how do I build in the 4th form? I think I do it by
doing this:

tblBranch
UID Name DominatedBySubFam DominatedByFam
1 Branch1 SubFamUID_3 FamUID_2

I hope I am not mucking up my explanation of the problem. Any advice
is sincerely appreciated.

Guy

On Oct 30, 8:36 pm, justaguy <your.mas... (AT) gmail (DOT) com> wrote:
You might want to take a look at nested sets.

On Oct 30, 8:36 pm, justaguy <your.mas... (AT) gmail (DOT) com> wrote:
You might want to take a look at nested sets.

On Oct 30, 8:36 pm, justaguy <your.mas... (AT) gmail (DOT) com> wrote:
You might want to take a look at nested sets.

NO! NO! and NO with a cherry on top! There is no such thing as a
magical UID in RDBMS -- do you also believe in the "Elixir of Life" of
Kabhalah numbers? The levels are attributes of the creature you are
categorizing, not entities by themselves.

Then Tables never have that silly "tbl-" prefix to violate both
ISO-11179 rules and basic data m

The Linnaen taxonomy levels are attributes of the creature you are
classifying, not entities in themselves.

Nope, totally wrong idea of 3NF.

Have you worked with Dewey Decimal in a library? Would you build a
table for the 100"s, one for the tens and one for the digits?

As I recall and my biology is waaaaay out of date, you have less than
ten levels that start at Kingdoms (Animal, Vegetable, Mineral?) so
that is not too many columns.

But you will want axillary tables that are referenced by the columns ,
so

CREATE TABLE NoahArk
(kingdom_name CHAR(10) NOT NULL
REFERENCES Kingdoms(kingdom_name),
..);

You might want to put the whole taxonomy into a Nested sets model, but
I don't know enough to advise you.

NO! NO! and NO with a cherry on top! There is no such thing as a
magical UID in RDBMS -- do you also believe in the "Elixir of Life" of
Kabhalah numbers? The levels are attributes of the creature you are
categorizing, not entities by themselves.

Then Tables never have that silly "tbl-" prefix to violate both
ISO-11179 rules and basic data m

The Linnaen taxonomy levels are attributes of the creature you are
classifying, not entities in themselves.

Nope, totally wrong idea of 3NF.

Have you worked with Dewey Decimal in a library? Would you build a
table for the 100"s, one for the tens and one for the digits?

As I recall and my biology is waaaaay out of date, you have less than
ten levels that start at Kingdoms (Animal, Vegetable, Mineral?) so
that is not too many columns.

But you will want axillary tables that are referenced by the columns ,
so

CREATE TABLE NoahArk
(kingdom_name CHAR(10) NOT NULL
REFERENCES Kingdoms(kingdom_name),
..);

You might want to put the whole taxonomy into a Nested sets model, but
I don't know enough to advise you.

NO! NO! and NO with a cherry on top! There is no such thing as a
magical UID in RDBMS -- do you also believe in the "Elixir of Life" of
Kabhalah numbers? The levels are attributes of the creature you are
categorizing, not entities by themselves.

Then Tables never have that silly "tbl-" prefix to violate both
ISO-11179 rules and basic data m

The Linnaen taxonomy levels are attributes of the creature you are
classifying, not entities in themselves.

Nope, totally wrong idea of 3NF.

Have you worked with Dewey Decimal in a library? Would you build a
table for the 100"s, one for the tens and one for the digits?

As I recall and my biology is waaaaay out of date, you have less than
ten levels that start at Kingdoms (Animal, Vegetable, Mineral?) so
that is not too many columns.

But you will want axillary tables that are referenced by the columns ,
so

CREATE TABLE NoahArk
(kingdom_name CHAR(10) NOT NULL
REFERENCES Kingdoms(kingdom_name),
..);

You might want to put the whole taxonomy into a Nested sets model, but
I don't know enough to advise you.

On Oct 30, 8:36*pm, justaguy <your.mas... (AT) gmail (DOT) com> wrote:
Hi,
I am really curious why you think third normal form requires a UID. I
see people making that assumption and I am just puzzled as to where
they (you in this case) got that notion. As you say, it was not from
formal training on database design.

Thanks,
Ed

On Oct 30, 8:36*pm, justaguy <your.mas... (AT) gmail (DOT) com> wrote:
Hi,
I am really curious why you think third normal form requires a UID. I
see people making that assumption and I am just puzzled as to where
they (you in this case) got that notion. As you say, it was not from
formal training on database design.

Thanks,
Ed

On Oct 30, 8:36*pm, justaguy <your.mas... (AT) gmail (DOT) com> wrote:
Hi,
I am really curious why you think third normal form requires a UID. I
see people making that assumption and I am just puzzled as to where
they (you in this case) got that notion. As you say, it was not from
formal training on database design.

Thanks,
Ed