mysql database normalization question

Question

here is my 3 tables:

table 1 -- stores user information and it has unique data

table 2 -- stores place category such as, toronto, ny, london, etc hence this is is also unique

table 3 -- has duplicate information. it stores all the places a user have been.

the 3 tables are linked or joined by these ids:

table 1 has an "table1_id"

table 2 has an "table2_id" and "place_name"

table 3 has an "table3_id", "table1_id", "place_name"

i have an interface where an admin sees all users. beside a user is "edit" button. clicking on that edit button allows you to edit a specific user in a form fields which has a multiple drop down box for "places".

if an admin edits a user and add 1 "places" for the user, i insert that information using php. if the admin decides to deselect that 1 "places" do i delete it or mark it as on and off? how about if the admin decides to select 2 "places" for the user; change the first "places" and add an additional "places". will my table just keep growing and will i have just redundant information?

thanks.

Answer 1

Tables 1 and 2 sound correct. Table 3 should not contain place_name. Instead, you should have foreign key references to both table1_id and table2_id (you can optionally keep table3_id). This makes table3 a junction table.

When you want to delete a place for a user, you'll delete a row in table_3 (and that's it). When adding a place, you first check if it exists in table 1. If not, you add it to table 1. Then, you add a row to table 3. A change (e.g. renaming a place in the UI for a user) should be broken down into a delete then add.

This will avoid redundant information.

Answer 2

How you handle this depends on the needs of your app. E.g., do you need to know when a place was deleted?

The simplest way is that whenever the places are being update for a user, jsut delete all of them and then re-add the ones that are selected.

So, if all places were de-selected and a new one added, it would look like:

delete from UserPlace where UserID = 42;
insert into UserPlace (UserID, PlaceID) values (42, 123);

Answer 3

Deleting records has the potential to affect concurrent inserts in MySQL, so having an on/off column rather than doing a DELETE can be an optimization down the road.

You can set a UNIQUE index on multiple columns, ensuring that only one record exists for any given person/location combination. The INSERT … ON DUPLICATE KEY UPDATE syntax can simplify queries in this situation, particularly when a location is re-enabled after having been deleted.

With this setup, you would want some maintenance script that runs every day/week/whatever to DELETE "off" records and then OPTIMIZE TABLE.

So, that's one way to do it.