Field values dependent on the value of another field?
I am designing a database that will act as a content management system for Web Map Services. I should say I am in no way a database expert and would appreciate your insight on a 'situation' I am encountering. These are some indicative entities:
There is a 1 to N relationship between Layer and the other two entities respectively (one layer can have multiple metadata and styles). Therefore, in the implementation, LayerMetadata and LayerStyles also have a foreign key field named 'layer_id'.
I would also like to have a 'configuration' table for a layer, that provides a unique combination of metadata and style for a specific layer:
The layer_id, meta_id and style_id are foreign keys to Layer, LayerMetadata and LayerStyles.
With this design, I ensure that the values of LayerConfig fields exist in the previous 3 tables. However, the values for meta_id and style_id in LayerConfig are really dependent on the value of layer_id and ideally the user shouldn't be allowed to insert any value within LayerMetadata and LayerStyles there. Would there be a good way to achieve this? I feel my approach is somewhat flawed...
@reaanb: This would make much sense for my model, but unfortunately I'm using an ORM software that does not allow for composite keys... In any case, I might be better off implementing it like that anyways; thanks a lot!