Database People

[ICRS]

Some of you responded A,B, and C should be in 1 table. They can't be, and here is why. I only used people as an analogy. THEY AREN'T PEOPLE, they are 3 diffrent things.

A is Bonds.
B is Float to Fixed Swap
C is Float to Float Swap
D are Sinkingfunds.

AB is a single bond fixed payer swap entity (Part of a Bond tied to Part of a Swap)
Table A:
Bond1
Bond2

Table B:
FixedPayerSwap1
FixedPayerSwap2

TableAB:
Bond1FixedPayerSwap1 (Joint with Bond1 and FixedPayerSwap1)
Bond1FixedPayerSwap2 (Joint with Bond1 and FixedPayerSwap2)
Bond2FixedPayerSwap2 (Joint with Bond2 and FixedPayerSwap2)

Table C:
BasisSwap1

Table ABC:
BasisSwap1Bond1FixedPayerSwap1 (Joint with Bond1FixedPayerSwap1 and BasisSwap1)


Table D:
SinkingFund1 (Tied to Bond1)
SinkingFund2 (Tied to Bond1FixedPayerSwap1 and Bond1)
SinkingFund3 (Tied to BasisSwap1Bond1FixedPayerSwap1, Bond1FixedPayerSwap1, and Bond1)
SinkingFund3 (Tied to Bond1FixedPayerSwap2 and Bond1)
SinkingFund4 (Tied to Bond2FixedPayerSwap2 and Bond2)

I already said it before I know normalization and this database is as normalized as it can become given the requirements.

 

[GeekDrew]

Originally posted by: ICRS
How would you handle this?

Probably easily, if I had enough information to understand what you're talking about.

 

[ICRS]

See bottom of page for question

 

[nakedfrog]

Originally posted by: GeekDrew

Originally posted by: ICRS
How would you handle this?

Probably easily, if I had enough information to understand what you're talking about.

Yeah, I think I'd have to see the data

 

[mugs]

Put an associative table between the tables that have many to many relationships.

i.e.

table: order
order_id
order_date
customer_id

table: item
item_id
item_name
item_desc

You want a many-to-many relationship between them. So you create an associative* table:

table: order_item
order_id
item_id
qty

* Just a regular table

 

[QED]

In tables D,E,F,G create 3 fields, called a_id, b_id, and c_id which contain the unique ids of the corresponding Table A, Table B, and Table C records (respectively) for that particular Table D, E, F, or G record.

To manage the relationships between the A, B, and C tables, create a new table called X, with 3 fields: a "from_id" field, a "to_id" field, and a type field. The type indicates which table the "from" and the "to" id field represent.

For instance, suppose a few records of Table X looked like:

from_id to_id type
1 1 A,B
1 2 A,B
1 3 A,B
1 1 A,C
2 1 B,A
2 3 B,A
3 5 B,A
1 1 C,A

From this, we know Records #1,2,3 from Table B "belong" to Record #1 from Table A. Record #1 from Table A "belongs" to Record #2 from Table B and Rcord #1 from Table C.

 

[MrChad]

Agree with both mugs and QED.

 

[sao123]

how about you just tell us what the subject of the database is?

Like what are the tables and fields?

 

[ICRS]

So my thread in OT was locked, I am assuming because I should have placed it here.


So here we go.

If you recall I wrote:

You have persons in Group A.

You have persons in Group B.

You have persons in Group C.


Each A, B, C are different and need to be stored in different tables.

People in group B must always enter into an agreement 1 or many people in group A. Two different people in group B can enter into an agreement with the same person in group A.
This agreement shall be referred the joint AB entity, and stored in table AB.

People in Group C are even more dependent, they must set up an agreement with 1 or more joint AB entity. Two different people in C can also set up an agreement with the same joint AB entity. This agreement shall be refered to as the joint ABC entity and stored in table ABC.

Here is what you have:

People in Group A (Table A)
People in Group B (Table AB)
People in Group C (Table C)

Joint agreement with a person in group A and B (Table AB)
Joint agreement with a person in group C and With a joint AB (Table ABC)


Now bank accounts D. (Table D):

Now we need to tie these bank accounts to EITHER
A person in table A.
A joint agreement between A and B.
A joint agreement between AB and C.

Some of you responded A,B, and C should be in 1 table. They can't be, and here is why. I only used people as an analogy. THEY AREN'T PEOPLE, they are 3 diffrent things.

A is Bonds.
B is Float to Fixed Swap
C is Float to Float Swap
D are Sinkingfunds.

AB is a single bond fixed payer swap entity (Part of a Bond tied to Part of a Swap)
Table A:
Bond1
Bond2

Table B:
FixedPayerSwap1
FixedPayerSwap2

TableAB:
Bond1FixedPayerSwap1 (Joint with Bond1 and FixedPayerSwap1)
Bond1FixedPayerSwap2 (Joint with Bond1 and FixedPayerSwap2)
Bond2FixedPayerSwap2 (Joint with Bond2 and FixedPayerSwap2)

Table C:
BasisSwap1

Table ABC:
BasisSwap1Bond1FixedPayerSwap1 (Joint with Bond1FixedPayerSwap1 and BasisSwap1)


Table D:
SinkingFund1 (Tied to Bond1)
SinkingFund2 (Tied to Bond1FixedPayerSwap1 and Bond1)
SinkingFund3 (Tied to BasisSwap1Bond1FixedPayerSwap1, Bond1FixedPayerSwap1, and Bond1)
SinkingFund3 (Tied to Bond1FixedPayerSwap2 and Bond1)
SinkingFund4 (Tied to Bond2FixedPayerSwap2 and Bond2)

I already said it before I know normalization and this database is as normalized as it can become given the requirements.

 

[BuncoBaggins]

If there is a one-to-many relationship from table A to table B, relate A to B. Otherwise, use a mapping table. Did I miss something? Isn't this just routine database design stuff?

 

[jread]

Originally posted by: mugs
Put an associative table between the tables that have many to many relationships.

i.e.

table: order
order_id
order_date
customer_id

table: item
item_id
item_name
item_desc

You want a many-to-many relationship between them. So you create an associative* table:

table: order_item
order_id
item_id
qty

* Just a regular table

You must have the same Oracle 10g book that I have

Anyway, you are exactly right: he needs to create associative tables. A many-to-many relationship technically cannot exist in a relational database.

 

[ICRS]

Originally posted by: BuncoBaggins
If there is a one-to-many relationship from table A to table B, relate A to B. Otherwise, use a mapping table. Did I miss something? Isn't this just routine database design stuff?

NO NO NO NO.

A to B is many to many, and I alread have a maping table (AB).

My question is about table D.

Some info.

This database MEETS 5th normalized form.
It is a requirement for this database to be a normalized as possible.

 

[GeekDrew]

Originally posted by: ICRS

Originally posted by: BuncoBaggins
If there is a one-to-many relationship from table A to table B, relate A to B. Otherwise, use a mapping table. Did I miss something? Isn't this just routine database design stuff?

NO NO NO NO.

A to B is many to many, and I alread have a maping table (AB).

My question is about table D.

Some info.

This database MEETS 5th normalized form.
It is a requirement for this database to be a normalized as possible.

If you know so much about normalization, and we are all apparently incapable of giving you advice based on the information you're giving us, what exactly is it you're looking for?