0

I have a few SQL tables, one for users, one for configurations, one for a key/value for users' configurations.

What I want to do is, when adding a new entry to the users table, I want to populate the key/value table with one row for all the available configurations rows in the configuration table.

So if the config table has these rows:

Setting1
Setting2
Setting3

I would want to end up with something like this for the key/value table:

NewUser | Seeting1 | OFF
NewUser | Seeting2 | OFF
NewUser | Seeting3 | OFF

Not sure how to actually add that to the key/value table (what the syntax is). I suppose it can be done as a trigger based on additions to the users table also.

sd_dracula
  • 3,544
  • 23
  • 77
  • 147
  • 2
    So are you wanting to just get a Select which will give you your design or...? – Fallenreaper Jul 14 '14 at 16:58
  • no schema...no queries....no efforts shown......you expect an answer...seriously???? – NoobEditor Jul 14 '14 at 17:00
  • Are you looking for a trigger? See if [the following post](http://stackoverflow.com/questions/4753878/how-to-program-a-mysql-trigger-to-insert-row-into-another-table) gives you a hint. – PM 77-1 Jul 14 '14 at 17:01

1 Answers1

2

You should be able to accomplish this in a single SQL by using an INSERT/SELECT statement. The one caveat is that you should store a column in the settings table called default_value that stores the initial setting value when a user is created. If you have that column you could create the default values like so:

INSERT INTO user_settings (user_id, setting_id, value)
SELECT
  <user_id>,
  id,
  default_value
FROM
  settings

You could definitely trigger this statement on INSERT into the users table.

Peter Nagel
  • 54
  • 1