Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2013
    Posts
    7

    Field values dependent on the value of another field?

    Hello,

    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:

    1. Layer (layer_id, name, abstract, url)
    2. LayerMetadata (meta_id, meta_url, format)
    3. LayerStyles (style_id, name, stylesheet_url)

    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:

    4. LayerConfig(config_id, layer_id, meta_id, style_id)

    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...

  2. #2
    Join Date
    Feb 2012
    Posts
    76
    Composite foreign key constraints might be what you're looking for, e.g.
    ...FOREIGN KEY (layer_id, meta_id) REFERENCES LayerMetadata (layer_id, meta_id)

  3. #3
    Join Date
    Jul 2013
    Posts
    7
    @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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •