Tables question ?

[tomo]

I have 2 datatables. One(PROJECTION) with fields PROJECTION_ID,
PROJECTION_COMPANY_NAME, and the second table(PROJECTION_VALUES) with fields
PROJECTION_FK, YEAR_ONE, YEAR_TWO.
Now, there is a big posibility that form on the web would need more then two
years(YEAR_ONE,YEAR_TWO), let say four year's ? Is it better to add two more
columns in the PROJECTION_VALUES table, or to
create a third table(lookup) named for example PROJECTION_YEARS and than
PROJECTION_VALUES will have FK to PROJECTION_YEARS, and a coulmn names for
example PROJECTION_VALUE ?

Thanks in advance.




__________ Information from ESET NOD32 Antivirus, version of virus signature database 5149 (20100527) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

 

[Tom Anderson]

I have 2 datatables. One(PROJECTION) with fields PROJECTION_ID,
PROJECTION_COMPANY_NAME, and the second table(PROJECTION_VALUES) with
fields PROJECTION_FK, YEAR_ONE, YEAR_TWO. Now, there is a big posibility
that form on the web would need more then two years(YEAR_ONE,YEAR_TWO),
let say four year's ? Is it better to add two more columns in the
PROJECTION_VALUES table, or to create a third table(lookup) named for
example PROJECTION_YEARS and than PROJECTION_VALUES will have FK to
PROJECTION_YEARS, and a coulmn names for example PROJECTION_VALUE ?

This question is about databases, not java, so it should go to a databases
group, not a java group. I've added a cross-post (with followup) to
comp.databases.

My answer would be that you definitely shouldn't add more columns to the
projection_values table. In fact, you shouldn't even have two. I don't
think i'd bother with a third table, though. I'd just do:

create table projection (
projection_id integer primary key,
projection_company_name varchar(255) not null unique
);

create table projection_value (
projection_id integer not null references projection,
year integer not null,
constraint projection_value_pk primary key (projection_id, year),
projection_value whatever
);

That's simple, normalised, and likely to be fast for simple lookup-type
queries ('find all projection values for the company called X', 'find the
projection value for the company with ID Z in year Y').

tom