PHP & MySQL: list of table entries in a second one

Question

I'm developping a website with a MySQL database containing 2 tables, one containing users information, a second containing gifts information. I want to store the information of who paid a part of the differents gifts and with which amount to give him the possibility to change it later.

I would add an entry to the gifts table with an array of users who donated and the amount. If someone want to change it later, I change the amount or remove the entry and update the displayed gift remaining amount.

But I do not think it is optimal to start with variable length arrays. Maybe creating a new table in the database with the donating user and amount for each gift would be better but it would also be more complicated to code and to manage (I would prefer to have only two tables).

Does someone have any suggestion regarding my dilemma?

EDIT: Based on @Lighthart suggestion I found a solution. I will create a table with a field for each gift. If a user donates for gifts, I create a new entry for the user and set an amount for the concerned gift field. If the user changes the amount, I update the entry. I can easily calculate the remaining amount by adding all the entries for one gift. If you have idea to still improve my solution, do not hesitate!

Lighthart · Accepted Answer · 2013-02-21 21:20:50Z

1

This needs to be solved with a many-to-many table if you are opposed to variable length arrays. This would be a special case of many-to-many (called association class) where you have data in addition to just the foreign keys.

answered Feb 21, 2013 at 21:20

Lighthart

3,6966 gold badges32 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

feronjb Over a year ago

I'm not opposed to variable length arrays. I just think ressource needs are bigger when searching for user ID in each gift array than searching for user ID in each gift table. Isn't it? I found this about many-to-many, if you have tutorial/documentation suggestion about many-to-many or variable length arrays, please share!

Mike Brant · Accepted Answer · 2013-02-21 21:24:17Z

0

You would probably want a number of tables for this. Off the top of my head I can think of:

users -> people in system (gist donors and recipients)
gifts -> list of gifts that can be given.
users_gifts -> table relating gifts that have been (or are being) given to the user who will be receiving them
users_gifts_donors -> table relating items in users_gifts to donors (users) and also storing information about the amount of donation towards to gift

answered Feb 21, 2013 at 21:24

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

Comments

Bridgey · Accepted Answer · 2013-02-21 21:27:39Z

0

I agree with Lighthart - this really is a three table solution: a table for users, a table for gifts, and a table for donations:

USER(userID [PK], firstName, lastName, email, passwordHash)
GIFT(giftID [PK], title, description, totalCost, dateAdded, owner [FK])
DONATION(donationID [PK], donatingUser [FK], giftDonatedTo [FK], amount, date)

[PK] = Primary Key, [FK] = Foreign Key

That way if somebody wanted to change their donated amount later, you would perform a SQL UPDATE on the particular donationID record.

answered Feb 21, 2013 at 21:27

Bridgey

5395 silver badges15 bronze badges

Collectives™ on Stack Overflow

PHP & MySQL: list of table entries in a second one

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related