How to relationship between Buyer and sponsor in database tables?

Question

0.00/5 (No votes)

See more:

Hi all.

In the our database , we have two Existence(table) :

1- Buyer
2- sponsor

each Buyer have one or two sponsor and each sponsor can be sponsor many buyer.
How do we design tables and relation between on it ???

thanks a lot.

Posted 11-May-15 4:37am

bernova

Updated 11-May-15 4:39am

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

F-ES Sitecore · Answer 1 · 2015-05-11T05:03:00

Solution 1

Have a Buyer table

[PK] Buyer_ID
Rest_Of_Buyer_Fields

a sponsor table

[PK] Sponsor_ID
Rest_Of_Sponsor_Fields

and a table that joins the two called Buyer_Sponsor

[PK] Buyer_ID
[PK] Sponsor_ID

This allows you to set up a many-to-many relationship between buyers and sponsors. If the buyer_sponser table data looks like

Buyer_ID, Sponsor_ID
1, 1
1, 2
3, 2
4, 9

then buyer 1 has sponsors 1 and 2. Sponsor 2 has buyers 1 and 3. Buyer 4 only has sponsor 9 and sponsor 9 only has buyer 4.

Posted 11-May-15 5:03am

F-ES Sitecore

Updated 11-May-15 21:10pm

v2

Comments

bernova 12-May-15 0:34am

which one of field is primary key??

and field that called called Buyer_Sponsor in which of table is defined??

F-ES Sitecore 12-May-15 3:11am

Sorry I meant a table called buyer_sponsor, not a field. I've also updated the answer to include primary keys, but if you wanted to add a key to buyer_sponsor it would be a composite key of both values (select both fields in Access's table editor and press the "key" button)

bernova 12-May-15 8:14am

this is good solution.

how to i define two sponsor in table that have same property(eg: personal code is primary key)??

with do this I faced an the following error :

"The changes you requested to the table were not successful because they would create duplicate values in the index"

my [PK] in sponsor table is personal code

F-ES Sitecore 12-May-15 8:32am

If you need duplicate values in the buyer_sponsor table, eg two entires with buyer_id = 100 and sponsor_id = 200 then you can't add a primary key to the table, you'll have to leave the table with no keys at all. If there are going to be a lot of entries in this table then you can add an index on each field instead to help with the performance that the key would have given you.

bernova 13-May-15 10:10am

thanks for your answer. this solution is worked for me.