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

Home -> Community -> Usenet -> c.d.o.server -> Re: Tables and ring-like data structure?

Re: Tables and ring-like data structure?

From: Jan Gelbrich <j_gelbrich_at_westfalen-blatt.de>
Date: Fri, 15 Aug 2003 08:52:58 +0200
Message-ID: <bhi04c$sngf$1@ID-152732.news.uni-berlin.de>


"Ramon F Herrera" <ramon_at_conexus.net> schrieb im Newsbeitrag news:c9bc36ff.0308141238.67d3feb7_at_posting.google.com...
> Jan:
>
> Thanks a lot for your reply. I am not sure I understand
> the actual implentation, though. It seems to me that you
> suggest I should create one table for every employee,
> something like this:
>
> PAYMENT_HISTORY_OF_<ssn here>
> paymente date;
> payment amount;
>

Terry has already replied on this.

> (is that it?) If that's the case I would end up with a
> really big number of tables. BTW: I have done that in the
> past, for performance. Is that good?

Definitely NO ! You would need only 1 table for all PERSONs, and only 1 table for PAYMENT_HISTORY.
PERSON.ID is then referenced in PAYMENT_HISTORY.PERS_ID to identify the person.
This is the foreign key to map the relationship between the tables.

It seems to me that You still have some difficulty to understand some fundamentals
of the relational data model, especially relations between tables and how they are set up.
Even when You have 1:N relationships, You still have 1+1=2 tables. When You have an N:M relationship for mapping all combinations between 2 tables,
which is derived to 1:N & M:1 in the physical data model, You have only 3 tables, not more.

> Am I breaking any rules
> by having lots and lots of identical tables?

It is then definitely not relational ;-) - and unfortunately I find *plenty* of those models out there |o(

>As you can
> probably tell, I don't have much experience doing the initial
> table design.
>

Do not worry, You are at least _willing_ to learn ! Search google for some fundamentals about "DATA MODELS", "conceptual data model design",
"physical data model design".
It is not so difficult as it may sound many times, in practice it is just asking the best questions:

I have 2 objects in my data model, X and Y Can object X reference to 1 or N objects Y ? -> 1: Keep it in the same table
-> N: You have found that You have 2 entities, split the former table into 2 and make an FK
(and do not forget about the indexes on them, THAT is going to mess up Your performance indeed !)

(e.g. one CDs contains many songs )
would then derive as

[table CD], CD.ID as PK -1:N- [table SONG], SONG.CD_ID as FK. or as an easier picture
CD <- SONG

When You have found 1:N : Can object Y reference to 1 or M objects X, too (the reverse question)?
->If Yes then You have found that all combinations are possible between X and Y
(e.g. a song can be on many CDs, and one CDs contains many songs ) would then derive as

[table CD] -1:N- [table CD2SONG] - M:1 - [table SONG] where CD2SONG only contains the 2 FK references to CD and SONG.

And that is basically all. The rest is the needs and the specs of Your data. hth, Jan

> TIA,
>
> -Ramon
>
>
>
> "Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message
news:<bhfdse$4no5$1_at_ID-152732.news.uni-berlin.de>...
> > Hi, Ramon,
> >
> > I donīt think it must be a ring, when I look on Your example,
> > it is simply an 1:N relationship from a PERSON (1) to a PAYMENT_HISTORY
(N),
> > which would then grow in steps of one record per person per day. Data
not
> > needed anymore could the be
> > extracted to another table e.g. OLD_PAYMENTS, or they could be deleted,
in
> > order to have almost only "actual" data
> > in the PAYMENT table.
> >
> > Thus You would not have a limitation to a number like 52, which
> > You would always have in a ring structure.
> >
> > hth, Jan
Received on Fri Aug 15 2003 - 01:52:58 CDT

Original text of this message

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