Mixing
How to make a table that could add many same number of different item?
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
1
down vote
favorite
I wanted to create a table to store coupons of users.
For example, there is 3 coupon which is A, B and C etc. A user can have 10 coupon A, 20 coupon B and 5 coupon C etc.
How should I create a table to store these? There are many users in the system. How should I create a table which able to store all these information?
If I add another table just for the user and the coupon I still need to add a table which can count the number of the coupon. I have 8 type of coupon. Can I just use a single table and store each coupon for each column and row for the number of coupon?
mysql database-design
edited Aug 19 at 9:58
Paul White♦
46.5k14251398
asked Aug 19 at 8:09
æŽ晓东
82
add a comment |Â
up vote
1
down vote
favorite
I wanted to create a table to store coupons of users.
For example, there is 3 coupon which is A, B and C etc. A user can have 10 coupon A, 20 coupon B and 5 coupon C etc.
How should I create a table to store these? There are many users in the system. How should I create a table which able to store all these information?
If I add another table just for the user and the coupon I still need to add a table which can count the number of the coupon. I have 8 type of coupon. Can I just use a single table and store each coupon for each column and row for the number of coupon?
mysql database-design
edited Aug 19 at 9:58
Paul White♦
46.5k14251398
asked Aug 19 at 8:09
æŽ晓东
82
it all depends on your requirements, let's say you need to how many coupons a user has, how many used, how many remaining? Think several scenarios you gonna face. Then we can design a table. Otherwise, you gonna end up in changing the schema later which will be difficult.
– Biju jose
Aug 19 at 8:52
owhhh ok now I realize that I forgot the coupon count should be in the third table thanks!!!!!
– æŽ晓东
Aug 19 at 8:57
add a comment |Â
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I wanted to create a table to store coupons of users.
For example, there is 3 coupon which is A, B and C etc. A user can have 10 coupon A, 20 coupon B and 5 coupon C etc.
How should I create a table to store these? There are many users in the system. How should I create a table which able to store all these information?
If I add another table just for the user and the coupon I still need to add a table which can count the number of the coupon. I have 8 type of coupon. Can I just use a single table and store each coupon for each column and row for the number of coupon?
mysql database-design
edited Aug 19 at 9:58
Paul White♦
46.5k14251398
asked Aug 19 at 8:09
æŽ晓东
82
I wanted to create a table to store coupons of users.
For example, there is 3 coupon which is A, B and C etc. A user can have 10 coupon A, 20 coupon B and 5 coupon C etc.
How should I create a table to store these? There are many users in the system. How should I create a table which able to store all these information?
If I add another table just for the user and the coupon I still need to add a table which can count the number of the coupon. I have 8 type of coupon. Can I just use a single table and store each coupon for each column and row for the number of coupon?
mysql database-design
edited Aug 19 at 9:58
Paul White♦
46.5k14251398
asked Aug 19 at 8:09
æŽ晓东
82
edited Aug 19 at 9:58
Paul White♦
46.5k14251398
edited Aug 19 at 9:58
Paul White♦
46.5k14251398
edited Aug 19 at 9:58
Paul White♦
46.5k14251398
46.5k14251398
asked Aug 19 at 8:09
æŽ晓东
82
asked Aug 19 at 8:09
æŽ晓东
82
asked Aug 19 at 8:09
æŽ晓东
82
82
it all depends on your requirements, let's say you need to how many coupons a user has, how many used, how many remaining? Think several scenarios you gonna face. Then we can design a table. Otherwise, you gonna end up in changing the schema later which will be difficult.
– Biju jose
Aug 19 at 8:52
owhhh ok now I realize that I forgot the coupon count should be in the third table thanks!!!!!
– æŽ晓东
Aug 19 at 8:57
add a comment |Â
it all depends on your requirements, let's say you need to how many coupons a user has, how many used, how many remaining? Think several scenarios you gonna face. Then we can design a table. Otherwise, you gonna end up in changing the schema later which will be difficult.
– Biju jose
Aug 19 at 8:52
owhhh ok now I realize that I forgot the coupon count should be in the third table thanks!!!!!
– æŽ晓东
Aug 19 at 8:57
it all depends on your requirements, let's say you need to how many coupons a user has, how many used, how many remaining? Think several scenarios you gonna face. Then we can design a table. Otherwise, you gonna end up in changing the schema later which will be difficult.
– Biju jose
Aug 19 at 8:52
it all depends on your requirements, let's say you need to how many coupons a user has, how many used, how many remaining? Think several scenarios you gonna face. Then we can design a table. Otherwise, you gonna end up in changing the schema later which will be difficult.
– Biju jose
Aug 19 at 8:52
owhhh ok now I realize that I forgot the coupon count should be in the third table thanks!!!!!
– æŽ晓东
Aug 19 at 8:57
owhhh ok now I realize that I forgot the coupon count should be in the third table thanks!!!!!
– æŽ晓东
Aug 19 at 8:57
add a comment |Â
1 Answer
1
active
oldest
votes
up vote
2
down vote
accepted
You can't do it correctly with a single table, more exactly your database will be redundant and seriously vulnerable against inconsistencies.
You should read about database normalization.
A normalized solution would be this:
- Table
users, columns:- user_id (integer, also primary key)
- ...any other user data, name etc...
- Table
coupons, columns:- coupon_id (integer, also primary key)
- ...any other coupon data, name, value, etc...
- Table
user_coupon(it is the important), it is a so named bridge table, columns:- user_id (foreign key to
users(user_id)) - coupon_id (foreign key to
coupons(coupon_id)) - count (integer, shows how many coupons with id
coupon_idhas the useruser_id)
- user_id (foreign key to
The important thing is that the primary key on table user_coupon must be user_id and coupon_id!
answered Aug 19 at 8:47
peterh
94621228
3
Comment discussion about exchanging personal contact information removed. Profiles may be used for this, but please bear in mind this is not how the SE model is intended to operate. Please see Any way to send a personal message to another user? on meta, and the linked FAQ.
– Paul White♦
Aug 19 at 9:55
add a comment |Â
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
accepted
You can't do it correctly with a single table, more exactly your database will be redundant and seriously vulnerable against inconsistencies.
You should read about database normalization.
A normalized solution would be this:
- Table
users, columns:- user_id (integer, also primary key)
- ...any other user data, name etc...
- Table
coupons, columns:- coupon_id (integer, also primary key)
- ...any other coupon data, name, value, etc...
- Table
user_coupon(it is the important), it is a so named bridge table, columns:- user_id (foreign key to
users(user_id)) - coupon_id (foreign key to
coupons(coupon_id)) - count (integer, shows how many coupons with id
coupon_idhas the useruser_id)
- user_id (foreign key to
The important thing is that the primary key on table user_coupon must be user_id and coupon_id!
answered Aug 19 at 8:47
peterh
94621228
3
Comment discussion about exchanging personal contact information removed. Profiles may be used for this, but please bear in mind this is not how the SE model is intended to operate. Please see Any way to send a personal message to another user? on meta, and the linked FAQ.
– Paul White♦
Aug 19 at 9:55
add a comment |Â
up vote
2
down vote
accepted
You can't do it correctly with a single table, more exactly your database will be redundant and seriously vulnerable against inconsistencies.
You should read about database normalization.
A normalized solution would be this:
- Table
users, columns:- user_id (integer, also primary key)
- ...any other user data, name etc...
- Table
coupons, columns:- coupon_id (integer, also primary key)
- ...any other coupon data, name, value, etc...
- Table
user_coupon(it is the important), it is a so named bridge table, columns:- user_id (foreign key to
users(user_id)) - coupon_id (foreign key to
coupons(coupon_id)) - count (integer, shows how many coupons with id
coupon_idhas the useruser_id)
- user_id (foreign key to
The important thing is that the primary key on table user_coupon must be user_id and coupon_id!
answered Aug 19 at 8:47
peterh
94621228
3
Comment discussion about exchanging personal contact information removed. Profiles may be used for this, but please bear in mind this is not how the SE model is intended to operate. Please see Any way to send a personal message to another user? on meta, and the linked FAQ.
– Paul White♦
Aug 19 at 9:55
add a comment |Â
up vote
2
down vote
accepted
up vote
2
down vote
accepted
You can't do it correctly with a single table, more exactly your database will be redundant and seriously vulnerable against inconsistencies.
You should read about database normalization.
A normalized solution would be this:
- Table
users, columns:- user_id (integer, also primary key)
- ...any other user data, name etc...
- Table
coupons, columns:- coupon_id (integer, also primary key)
- ...any other coupon data, name, value, etc...
- Table
user_coupon(it is the important), it is a so named bridge table, columns:- user_id (foreign key to
users(user_id)) - coupon_id (foreign key to
coupons(coupon_id)) - count (integer, shows how many coupons with id
coupon_idhas the useruser_id)
- user_id (foreign key to
The important thing is that the primary key on table user_coupon must be user_id and coupon_id!
answered Aug 19 at 8:47
peterh
94621228
You can't do it correctly with a single table, more exactly your database will be redundant and seriously vulnerable against inconsistencies.
You should read about database normalization.
A normalized solution would be this:
- Table
users, columns:- user_id (integer, also primary key)
- ...any other user data, name etc...
- Table
coupons, columns:- coupon_id (integer, also primary key)
- ...any other coupon data, name, value, etc...
- Table
user_coupon(it is the important), it is a so named bridge table, columns:- user_id (foreign key to
users(user_id)) - coupon_id (foreign key to
coupons(coupon_id)) - count (integer, shows how many coupons with id
coupon_idhas the useruser_id)
- user_id (foreign key to
The important thing is that the primary key on table user_coupon must be user_id and coupon_id!
answered Aug 19 at 8:47
peterh
94621228
answered Aug 19 at 8:47
peterh
94621228
answered Aug 19 at 8:47
peterh
94621228
answered Aug 19 at 8:47
peterh
94621228
94621228
3
Comment discussion about exchanging personal contact information removed. Profiles may be used for this, but please bear in mind this is not how the SE model is intended to operate. Please see Any way to send a personal message to another user? on meta, and the linked FAQ.
– Paul White♦
Aug 19 at 9:55
add a comment |Â
3
Comment discussion about exchanging personal contact information removed. Profiles may be used for this, but please bear in mind this is not how the SE model is intended to operate. Please see Any way to send a personal message to another user? on meta, and the linked FAQ.
– Paul White♦
Aug 19 at 9:55
3
3
Comment discussion about exchanging personal contact information removed. Profiles may be used for this, but please bear in mind this is not how the SE model is intended to operate. Please see Any way to send a personal message to another user? on meta, and the linked FAQ.
– Paul White♦
Aug 19 at 9:55
Comment discussion about exchanging personal contact information removed. Profiles may be used for this, but please bear in mind this is not how the SE model is intended to operate. Please see Any way to send a personal message to another user? on meta, and the linked FAQ.
– Paul White♦
Aug 19 at 9:55
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f215301%2fhow-to-make-a-table-that-could-add-many-same-number-of-different-item%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
it all depends on your requirements, let's say you need to how many coupons a user has, how many used, how many remaining? Think several scenarios you gonna face. Then we can design a table. Otherwise, you gonna end up in changing the schema later which will be difficult.
– Biju jose
Aug 19 at 8:52
owhhh ok now I realize that I forgot the coupon count should be in the third table thanks!!!!!
– æŽ晓东
Aug 19 at 8:57