views:

22

answers:

1

Newbie with databases, I would like some advise please..
I have agencies who can download photo's.
Standard each agency can download "medium" & "large" photos.
Now from their account page I would like them to make extra custom presets and manage those.

I looked in the database of some blog software how they handle categories and wrapped my head around this example. Is this the right approach?
Cheers

agency 1 has preset "medium" & "large"
agency 2 has preset "medium", "large" & "Bill custom"

-----------
| presets |
-----------------------------------------------
| preset_id | preset_name | preset_dimensions |
-----------------------------------------------
|     1     |    medium   |      800x600      |
|     2     |    large    |     3000x2000     |
|     3     | Bill custom |      640x420      |
-----------------------------------------------

----------------
| preset_assoc |
------------------------------------------------------------
| presassoc_id | presassoc_preset_id | presassoc_agency_id |
------------------------------------------------------------
|       1      |           1         |          1          |
|       2      |           2         |          1          |
|       3      |           1         |          2          |
|       4      |           2         |          2          |
|       5      |           3         |          2          |
------------------------------------------------------------
------------
| agencies |
---------------------------
| agency_id | agency_name |
---------------------------
|     1     |   Joe ltd   |
|     2     |   Bill inc  |
---------------------------
+2  A: 

The approach is right. Because you have NxN relation (1 agency can have multiple presets, and the same preset could be used by multiple agencies) you need to have a joining table. The only questionable thing is that preset_assoc doesn't have to have presassoc_id because the other 2 columns could be used as a combined primary key.

Ivan Ferić
Thanks for the thoughts. But if preset_assoc doesn't have an id, how can I delete the preset if an agency wants to?
FFish
DELETE FROM preset_assoc WHERE presassoc_preset_id = <PRESET_ID_TO_DELETE> AND presassoc_agency_id = <AGENCY_ID_WHICH_WANTS_TO_DELETE_PRESET>
Ivan Ferić
that's great, cheers Ivan!
FFish