0

I have a table like this:

table1

+----+-----------+---+---+
| ID |   Type    | a | b |
+----+-----------+---+---+
|  1 | Location  | ? | ? |
|  2 | Equipment | ? | ? |
|  3 | Location  | ? | ? |
+----+-----------+---+---+

I want the columns a and b to be limited to a set of values, depending on the value of the Type column. The possible values should be stored in another table with the name of the Type.

So for example: ID 1 has Type Location, so the columns a and b can only take values from table Location_table, which could look like this

Location_table

+----+----+
| a  | b  |
+----+----+
| a1 | b1 |
| a2 | b2 |
+----+----+

So column a could take the values a1 and a2.

My question is: is this in any way possible, if so, how?

edit: I realised that the "lookup" table isn't exactly what I wanted. I will have a table "a" and a table "b".

Table a

+----+-----------+-------+
| ID |   Type    | Value |
+----+-----------+-------+
|  1 | Location  | a1    |
|  2 | Location  | a2    |
|  3 | Equipment | a3    |
+----+-----------+-------+

Table b

+----+-----------+-------+
| ID |   Type    | Value |
+----+-----------+-------+
|  1 | Location  | b1    |
|  2 | Equipment | b2    |
|  3 | Equipment | b3    |
+----+-----------+-------+

So if an entry in table1 is of Type "Location", column "a" could get values "a1" and "a2" and column "b" can get value "b1".

I think that's more like it.

Decay42
  • 802
  • 1
  • 9
  • 20
  • As it stands now, your proposed schema is not _relational_, since there is no way to connect a `Type` in `table1` to a row in the `Location_table`. – Tim Biegeleisen Sep 07 '15 at 07:30
  • possible duplicate of [MySQL - Conditional Foreign Key Constraints](http://stackoverflow.com/questions/2002985/mysql-conditional-foreign-key-constraints) – vhu Sep 07 '15 at 07:31

0 Answers0