0

Background

I have a relational database an I want to have a table that contains flags. I want multiple tables in my database to have a many to many relationship with the flag table.

Options

I could go about this two ways (I think):

  1. I could create a join table between the flag table and each table I want it to have a many to many relationship with.
|Table1ID|FlagID|    |Table2ID|FlagID|    |Table3ID|FlagID|
|--------+------|    |--------+------|    |--------+------|
|   1    |  4   |    |   4    |  2   |    |   8    |  3   |
|  ...   | ...  |    |  ...   | ...  |    |  ...   | ...  |
  1. I could create a join table that listed the table, the row id and the id for the flag.
|TableName|ID|FlagID|
|---------+--+------|
| Table1  |1 |  4   |
| Table2  |4 |  2   |
| Table3  |8 |  3   |
|  ...    |..| ...  |

Question

Obviously, I would like to go with option two. Is there any reason I shouldn't do this?

  • Why is option 2 the obvious choice? – Robert Harvey Jun 25 at 21:37
  • I think option 1 is actually the more sensible choice. I just want to know if I could reduce the number of lookup tables since they all have one in common. Ie, I'm lazy. – cOborski Jun 25 at 21:47
  • I would probably go with option two myself. However, it should be noted that option two involves storing database metadata, namely table names, in a user table. There are downsides to this but they will probably never become real problems for you. – Walter Mitty Jun 26 at 13:22
1

In the past, when I needed this capability, I employed a Generic Key strategy. It looks like this:

Generic Key Type Table

GenericKeyTypeID   TableName    FieldName
       1            Table1      FlagID
       2            Table2      FlagID
       3            Table3      FlagID

You can have as many combinations of table and field in your Generic Key Type table as you want.

Then, in your linking table, you create two fields: GenericKeyID and GenericKeyTypeID. In the GenericKeyTypeID field, you put the ID corresponding to one of the combinations above. In the GenericKeyID, you put the ID of the corresponding target table record.

So for example, your Table1 example would look like this:

GenericKeyTypeID   GenericKeyID
      1                4

This will work for any field combination. It is not specific to your Flag field.

  • Thanks! I like this idea better because it means I can have flags that only pertain to some columns and not others (although I'm not sure that will happen.) – cOborski Jun 26 at 16:15
  • Do you ever have problems with storing database metadata like @WalterMitty mentioned? That was actually the thing the concerned me the most about my "option 2" and why I said that just using join tables was more sensible. – cOborski Jun 26 at 16:19
  • Not really. It's just names. – Robert Harvey Jun 26 at 17:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.