CODEWITHSUNDEEP
phpmysqldatabase-design
james
james

Reputation: 2663

Php Mysql Updates

I have a database where a user can enter any number of interests. When they go to update it they can remove one of their former interests.

How should this be handled? Should an update delete all their former interests from the database and then insert their updated ones?

Upvotes: 1

Views: 57

Answers (2)

SamT
SamT

Reputation: 10610

If your pool of interests are not stored in a separate table, you can use the flag approach:

Users Table (example)

user_id int(8) AUTO_INCREMENT
user_name varchar(255)
user_email varchar(255)

User Interests table (example)

interest_name varchar(255)
user_id int(8)
interest_status tinyint(1)

The interest status could be a 1 (for active interest) or a 0 (for former interest)

On the flip side, if you have a pool of interests too choose from and a binary association table, then change the interest_name to the interst_id it represents. Delete all the old records and reinsert them with the problem flags. It's usually the easier thing to do and is less error prone.

Upvotes: 1

Scott Saunders
Scott Saunders

Reputation: 30394

That's what I usually do: Delete all the existing records and add the new ones. It's the simplest thing to do if it's a simple cross reference table. If there are id fields that are important you might have to do something else.

Upvotes: 1

Related Questions