2

i have a question about the best practices about this situation: I have a mysql table that contains (besides the other columns) two columns that are both TINYINT(1) but they can't be both on 0, they can be 0 and 1, 1 and 0, 1 and 1 but no 0 and 0. What's the best way to control this? is even a way to control this?

Edit: i think i will choose the ENUM option but how this will perform if i combine mysql with Entity Framework?

Phoenix_uy
  • 3,173
  • 9
  • 53
  • 100
  • That's a tough one. What are the columns for? Knowing how they'll actually be used may help people suggest solutions. – Dan J Jun 20 '13 at 18:43
  • The columns are for a system delivery configuration, the values are is_physical_delivery and the other is is_digital_delivery so can be one, the other or both – Phoenix_uy Jun 20 '13 at 18:49
  • Then I would probably agree with the answers suggesting an enum that contains the possible values, in a single column named like `delivery_configuration`. :) – Dan J Jun 20 '13 at 18:49
  • For this case, I agree... something like this: `ALTER table ADD delivery ENUM('physical', 'digital', 'both') NOT NULL` would describe the data accurately. – landons Jun 20 '13 at 19:08
  • Did you end up going with `SET` or `ENUM`? – landons Jun 20 '13 at 19:34

4 Answers4

3

I would suggest using an enum, combined from both values. They are stored quite efficiently. So it would be enum('0;1','1;0','1;1') not null.

EDIT:

Given the additional information, and following @landons suggestion, just for the record: the enum would be 

delivery enum('physical', 'digital', 'both') not null;

You could use (also following @landon) 

delivery SET('physical','digital');

But that would allow empty values.

flaschenpost
  • 2,205
  • 1
  • 14
  • 29
3

A few options I can think of:

  • Validate in before update/insert triggers
  • Validate in application logic
  • Add a foreign key reference with acceptable states
  • Use an ENUM with all the possible combinations, and don't allow NULL

I would probably use the last approach, personally.

EDIT:

Just realized--the SET data type accomplishes this perfectly.

landons
  • 9,502
  • 3
  • 33
  • 46
  • SET is cool and fast, but in this case it would allow all kind of invalid values. – flaschenpost Jun 20 '13 at 18:55
  • @landons Set('0','1') would not allow '1','1'. Set('01','10','11') would allow the combined value '01','10','11'. Which set do you mean? – flaschenpost Jun 20 '13 at 19:09
  • I mean `SET('digital', 'physical') NOT NULL` would allow "digital," "physical" or "digital,physical" – landons Jun 20 '13 at 19:10
  • @landons ah, that makes more sense then the original 0,1. So the honour is yours to ask the right question and give a much better answer then the original question was. But even in a not null SET you can store empty values without even a warning. I know an enum also stores simply an empty string when not in strict mode and fed with a wrong value, but that at least gives a warning. – flaschenpost Jun 20 '13 at 19:58
  • @flaschenpost The original question didn't account for putting in a 2 ;). This isn't a replacement for proper validation. – landons Jun 20 '13 at 20:03
0

One workaround would be to combine your two columns into a BIT(2) column, then when you perform your update you could test that you weren't updating the column to b'00'.

As for a built-in prevention, I do not believe there is a way to do it.

Blake A. Nichols
  • 870
  • 1
  • 6
  • 11
0

Perhaps you could achieve this by putting them together in 1 column.

  • first number represents 1
  • second number represents 2

Your column could then contain values 1, 2 or 3. When it is 0, they both are.

With 2 and 3 the second number would be 1. With 1 and 3 the first number would be 1.

Rene Pot
  • 24,681
  • 7
  • 68
  • 92