A database design. Transitive dependency or not?
I took a long (over a year) break from database design and im just comming back. I am designing a database for the website that i am creating. I am storing 2 boolean values in one table (two separate columns). I realized that if first value is true the other one will also always be true but if the first value is false the other one can be true or false. As far as im concerned this is not a transitive dependency and I shouldn't create a new table but I want to make sure that I'm doing everything right. If there were multiple values that behave like the second boolean value would I still keep it in the same table? What is the best way to store this kind of data in a database?
I would appreciate if the anwser included an ER Diagram.
Normalized: I think we should distinguish two cases:
- max length of a sequence of boolean values == 2;
- max length of a sequence of boolean values > 2;
The first case can be solved using a single table with two fields, since it guarantees both good performance and space optimization (you can't save more space than this way, except if you ignore Normalization Rules, which I did in the last of these solution):
MyBools (id, firstBool, secondBool);
Concerning the second case I came up with two ideas, none of which I'm satisfied with. The main thing we can say is that when you have long sequences, a column for each boolean value isn't very handy. Here are my two ideas:
a single table with a PK, a boolean field and a self-referencing foreign key:
MyBools (id, thisBool, idNextBool);
thisBool clearly contains a boolean value. If thisBool is true you're done, you don't need to store the following boolean value, since its value matches the first one. If thisBool is false idNextBool points to the following boolean. This solution allows forward-search only.
a single table with a PK, a boolean value and a self-referencing foreign key:
MyBools (id, thisBool, idNextBool);
If idNextBool is null, you've reached the first value of the sequence. Otherwise idNextBool points to the following boolean. This solution allows backward-search only.
As you can see the solutions for the second case (sequences longer than 2 values) are rather un-manageable. That's why I propose a non-normalized solution.
Not Normalized: You could treat these boolean values as bits of a numerical field (especially considering that a boolean is in fact represented by a bit). Let's say we have two fields, field1 and field2, and consider that we can put them in one single field (let's call it myfield):
1) IF field1 is True THEN field2 True myfield = 0 __ True myfield = 10 2) IF field1 is False THEN field2 / \__ False myfield = 11
As you can see you can extend this to as many boolean values as you can fit inside a numerical field (for example in a 32bit numerical field you can store 32 boolean values, as long as each one depends on the lower ones).
It is not a functional dependency, therefore it is not a transitive functional dependency.
By definition, X -> Y if, and only if, each X value is associated with precisely one Y value.
In your case, when X is false, the Y could be ether true or false, so the definition above doesn't hold.
It does look like a multi-valued dependency though.
I'm not sure you should do anything about it other than adding appropriate CHECK constraint (cost of complicating things would be to high for the expected gains).
I thought of a solution but i dont know if its any good. If the first value is false i could make another table for all the other values with a FK and store the other values if the value is true i could just not generate the table for those values because they will all be true anyways
Your question is a bit vague, but I'll give it a shot. First, from what you describe, you do not have a transitive dependency. The reason is, you mention only two, what I am assuming to be attributes in your table, when a transitive dependency would need three attributes to exist.
Transitive dependency exists when A implies B, and B implies C. In other words, A implies C transitively through B.
Anyway, I think looking for functional dependencies with small data sample and no context leads to poor database design. What are the elements in your table?