Database People?

[ICRS]

Here is an image of what I am trying to do I can't explain it well in text, so look at the diagram. Any help is appreciated, so thanks in advance.

Database help


Hope this will clear it up.

Think of it like this.

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.



-----------------------------
There are proper forums for such questions.

This is the second time you have posted in OT on this subject.

This thread will not be moved.
The next time you will be given a chance to review the forum guidelines without the distraction of posting.

Senior Anandtech Moderator
Common Courtesy

 

[Saint Nick]

Thats quite possibly the most confusing ER diagram I've ever seen, same with the business rules listed below it

 

[Bill Brasky]

wtf diagram

 

[Zach]

I have seen way worse..

Wouldn't you make all the letters the primary keys (serial ints) and add columns as needed to reference the other tables?

 

[KLin]

What's wrong with the previous thread you posted that got moved to the programming forum?

 

[Skeeedunt]

Just throw that shit in Excel and you're golden.

 

[XZeroII]

I see your problem. A, B, C and D need to be renamed to Alf, BigBoy, Charles and Doggie.

Glad to help...anytime.

 

[I Saw OJ]

Originally posted by: XZeroII
I see your problem. A, B, C and D need to be renamed to Alf, BigBoy, Charles and Doggie.

Glad to help...anytime.

Win

 

[wiredspider]

wow...i have no idea, guess i won't be able to cut it as a dba ever...

 

[alkemyst]

WTF are you trying to accomplish with this schema?

 

[Axoliien]

Yeah I don't quite get it... Why is this not a standard ER diagram with business intelligence through design or triggers, etc? I mean I get you want your data to be stored in a certain way, but eh? Oh yeah, and ditto to alkemyst...

 

[Descartes]

That's one of the most perplexing descriptions I think I've ever read. That said, in parsing what was written in about 10 seconds, it sounds like you're trying to accomplish what's called a junction table. Googling around will produce plenty of results for you on that.

 

[LuckyTaxi]

Originally posted by: Skeeedunt
Just throw that shit in Excel and you're golden.

QFT

 

[ICRS]

Originally posted by: alkemyst
WTF are you trying to accomplish with this schema?

Something complex.

These are hard business relationship between each table. The database MUST capture this relationship in full.

 

[ICRS]

Originally posted by: alkemyst
WTF are you trying to accomplish with this schema?

Something complex.

These are hard business relationship between each table. The database MUST capture this relationship in full.

The ER table is directly from SQL 2005, I just redid it in paint. Also it is only part of the whole database which has over 100 tables and relationships all over the place.

 

[ICRS]

Some info. We are also making the database as normalized as possible. It is actually very normalized.

 

[ICRS]

Hope this will clear it up.

Think of it like this.

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.

 

[sao123]

Originally posted by: ICRS
Hope this will clear it up.

Think of it like this.

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.

Ill say the same thing i said in your otheer thread... this relationship might make more sense if you could explain it in the business case..

no matter, one of the D relationships is going to have the be the primary on, otherwise you will have cyclical redundancy and your queries will fail.

 

[mugs]

Originally posted by: ICRS
Hope this will clear it up.

Think of it like this.

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.

Is an AB agreement always one to one? Meaning person B1 has an agreement with A1, and another agreement with A2? Or are persons B1, B2, B43, A4, A7, A9 all in a single agreement together (as well as possibly other agreements with other groups)?

Same question with C.

 

[OdiN]

Okay....I'm a Database Analyst by day...and you're confusing me.

Mainly because I have NO CLUE what you're trying to accomplish. A, B, C, D, AB, A, CA, DA, ABC....wtf?

There are not data types, nor any real information about what's in the tables. How do I even know if your design is correct? There may be a MUCH simpler way to organize the data so that you can accomplish what you need. That diagram doesn't help.

It really sounds like a mess. I would need to know what kind of data you're storing, what you're actually trying to do with the data. Not this esoteric A, B, C crap.

Why are A, B, C different and need to be stored in different tables? You have people in each one - okay so what - why can't you have a Department ID or some identifier to show which department they work in or something like that? Then you'd reduce those 3 tables down to 1 table with people in it. Less confusing.

What is this "agreement" you're talking about?

We really need more information in order to help you.

 

[torpid]

Why do A B C need separate main tables? People should be in a people table, and people groups should be in a different table. Two tables total, one to house people, and one to say if they are A B C. Or if they can only be in one group, one table.

 

[OdiN]

Originally posted by: torpid
Why do A B C need separate main tables? People should be in a people table, and people groups should be in a different table. Two tables total, one to house people, and one to say if they are A B C. Or if they can only be in one group, one table.

Yeah I think the OP needs to read up on DB normalization before he gets too far into something like this.

He may not be able to normalize for some reason, but we need to know why.