Re: Tables question ?

#1

On Thu, 27 May 2010, tomo wrote:

Quote:

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

#2

tomo wrote:

Quote:

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 ?

Tom Anderson wrote:

Quote:

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').

tomo, your keyword is "normal", as in database "normal form".

There's a good Wikipedia article on normal forms.

To someone familiar with normal forms, tom's answer leaps out for the reason
your question states - if you don't know how many entries some attribute
takes, you cannot properly model that attribute with multiple columns.

--
Lew

#3

Tomo does not even the right words for RDMS. Columns are not fields,
etc.

"A problem well stated is a problem half solved." -- Charles F.
Kettering

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. Temporal data should use ISO-8601
formats. Code should be in Standard SQL as much as possible and not
local dialect.

Sample data is also a good idea, along with clear specifications. It
is very hard to debug code when you do not let us see it. If you want
to learn how to ask a question on a Newsgroup, look at:
http://www.catb.org/~esr/faqs/smart-questions.html

If you don't know anything about RDBMS, then get a copy of the
simplest intro book I know --
http://www.amazon.com/Manga-Guide-Da.../dp/1593271905

If you want a simple introduction to thinking in sets instead of
sequential file structures,look at:
http://sqluniversity.net/media.php?m...Sets.rm&pid=57

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Tables question ?

comp.databases comp.databases

Re: Tables question ? - 05-27-2010 , 12:31 PM

Re: Tables question ? - 05-27-2010 , 05:53 PM

Re: Tables question ? - 06-03-2010 , 04:47 PM