Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Correct way to model this?

Correct way to model this?

From: crumedgeon <zimsbait_at_hotmail.com>
Date: 2000/07/11
Message-ID: <8kgifj$763$1@nntp9.atl.mindspring.net>#1/1

Hello all,

I have a set of tables in my database. One is a target table, another is a "scratch pad" table where users will try "what-if" scenarios. They both contain target demographic data in them. There is a primary, secondary and tertiary set of demographics for each row in the table.

Here is the target table (the scratch pad is similar):

create table target_demographics
(

id                                    int, (PK)
primary_demographic      int (FK to demographics.demo_id),
secondary_demographic  int (FK to demographics.demo_id),
tertiary_demographic       int (FK to demographics.demo_id),
other table row data......(not related to the demographic table) )

the demographics table looks like:
create table demographics
(

demo_id                       int (PK),
description                    varchar(20),
low_range                    int,
high_range                   int

)

The data here would look something like: (and this is made up data..)
(*note*, sex 'M','F' is being decided on to be added to the demo
table also) The ranges are also being debated on a "user requirement" basis. (it seems to make more sense to add them as a varchar like '18-24', since nothing special is happening to those values)

(demographics table)
(1,'teen',13,17)
(2,'young adult',18,24)
(3,'adult',25,50)
(4,'senior',51,100)

(target table)
(1,1,2,3),
(2,2,3,1),
(3,1,4,2)

Is it correct to have the FK's in the target_table (and the scratch pad table), or should there be another table, called "targets" having the primary, secondary and tertiary data (linking to the demographics table) in it? Since it is a standard
to only shoot for 3 demographic groups in the app, would it be ok to leave the FK data in them? The rules state in this case, only one demographic can be used per target (i.e. you cannot have the 'young adult' be your primary and tertiary targeted demographic, it can only be one or the other.) I know having 3 demographics
tables (primary, second, tertiary) is ridiculous, and normalizing this out to another child
table doesn't make sense, since there is a set number of targets (1,2,3) you will
always be shooting for.

This one's got me stumped.

Thanks in advance,
//
cr Received on Tue Jul 11 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US