From: tomo on 27 May 2010 09:54 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
From: Tom Anderson on 27 May 2010 12:31 On Thu, 27 May 2010, tomo wrote: > 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 -- the themes of time-travel, dreams, madness, and destiny are inextricably confused
|
Pages: 1 Prev: Top 10 Technical requirements for In-Memory Reporting Next: Strange System.arraycopy behaviour |