CODEWITHSUNDEEP
mysqldatabasedatabase-designphpmyadmin
thegrede
thegrede

Reputation: 494

How to map two tables in database PHPMyAdmin

I'm working on a project in which a user can save their own coupon codes on the website, so I want to know what is the best way to do that.

Lets say, I have 1 table with the users, like this:

userId  |  firstName  | lastName  | codeId

And then I have a table of the coupon codes, like this:

codeId  |  codeNumber

So what I can do is to connect the codeId to userId so when someone saves the coupons the codeId from the coupon table goes into the codeId of the users table.

But now what do I do if when user has multiple coupons? How should they be connected to the user?

I have 2 options what to do:

  1. Saving the codeId from coupons table into the codeId of users table like 1,2,3,4,5,

  2. Make a new row into the coupons table and connect the user to the code by adding another field in the coupon table userId and putting into it the userId of the user which has added the coupon.

So which of the two options is better?

Upvotes: 0

Views: 2105

Answers (3)

Emil Vikström
Emil Vikström

Reputation: 91922

A many-to-many relationship between two tables should be solved by adding a third table connecting the two:

user_coupons
- userId
- couponId

Upvotes: 2

Ddo
Ddo

Reputation: 395

If i was you my database will be:

userId  |  firstName  | lastName

codeNumber | userId

You can insert many codeNumber for any user if you want.

Upvotes: 0

srini.venigalla
srini.venigalla

Reputation: 5145

Your first option violates the 1-NF rule, where it says that no column will have a group of values.

This is a typical one-to-many relationship. Your second option of creating a separate table User_Coupons (not the the underscore) and create a row with UserID, CouponCode, for each association. The two columns together should be unique so that they both become the primary key of the associative table.

Upvotes: 1

Related Questions