To keep a database normalized should a one to many relationship always use two tables? The data in the following table is used as a master record, once it's setup it will rarely be changed or have more rows added to it.

create table tab1
(account integer,
field2 char(40))

There will be about 4 unique values for tab1.account and about 3 associatedrows for tab1.field2. Since the data set is so small would it be better to just put it all into one table with two columns rather than two tables each with two columns? Normally here is how I would do it:

create table tab1
(account integer,
link serial)

create table tab1_link
(link integer, -- links to tab1.link
field2 char(40))
~
~

Actually, since the key to tab1 is already an integer, there is no value
gained to introducing an artificial serial type key, so I would normally
just do:

create table tab1 (
account integer
);

create tablet tab2 (
account integer,
field2 char(40)
);

alter table tab1 add constraint primary key(account);
alter table tab2 add constraint foreign key(account) references
tab1(account);

However, as you point out, there will only be a total of 12 rows in the
result set when these two tables are joined, so there isn't much harm in
denormalizing this a bit and making it just one table. It will eliminate
the overhead of the foreign key index on tab2 (which is optional beginning
in Informix v11.70 anyway). You should still probably have an index on
account (though not a unique index) or better just a single unique index on
account and field2.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.



On Mon, Apr 16, 2012 at 10:03 AM, loc <c320sky (AT) gmail (DOT) com> wrote:

On Monday, April 16, 2012 1:24:47 PM UTC-4, Art S. Kagel wrote:
Art,

If it were your database would you use just the one table or two? I guess my question is should I use two tables just for the sake of keeping things normalized? Would having only one table even qualify as denormalized, since there isn't any data duplication, except the primary key which would be anyway.

I would keep i normalized in two tables. Make sure you have a unique key
on the tab2 table to prevent anyone from entering the same value twice.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.



On Mon, Apr 16, 2012 at 2:22 PM, loc <c320sky (AT) gmail (DOT) com> wrote: