December 13, 2016 at 8:22 am
I have a large number of yes/no columns for a table. Rather than creating a bit field for each, I'd like to use a bitwise, binary column. However, one of the requirements is that if the bit is NULL, then look to the parent record. Is there a good way of implementing a tristate bit with a bitwise column? I'm currently considering using 2 bitwise columns. If the first has the value, then the result is 1, if the second has the value, then the result is 0. The second column could also indicate a NULL.
I was also thinking of using a computed column to indicate what the values are, but I'd need to figure out how to check higher nodes with a calculated column.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 13, 2016 at 8:44 am
A BIT datatype can be NULL.
Also, an integer columns can be used to contain multiple bitwise values (TINYINT 8, SMALLINT 16, INT 32, BIGINT 64).
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 13, 2016 at 8:48 am
Part of the reason that bits were introduced was because of the complexity of using bitfields and bitwise computations. You're already seeing some of the issues, in that you can't have three-value states. Why do you think that bitfields are a better approach than bits?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 13, 2016 at 8:52 am
Why would you want to complicate things? Sure, you'll get less columns, but you'll need to make your code more confusing. A bit column will take just a bit of storage, so there's no real reason to hide everything in binary or integer columns.
December 13, 2016 at 9:02 am
I want to use a bitwise column because there may be more columns added in the future. With a bitwise column, you just add a new value. With bit columns you have to change the structure of the table which requires more oversight, upgrades, etc...
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 13, 2016 at 10:37 am
Hey, long-time no hear from.
Any chance you could normalize the structure so the Yes/No columns are in a separate or separate table(s). Maybe even an EAV table, which will eliminate the need to add columns later.
I'm not a big fan of bitwise columns because they aren't clear and, in my experience, aren't documented well enough for the next person who has to support it (yes, I have been the next person).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 13, 2016 at 10:42 am
Sioban Krzywicki (12/13/2016)
I want to use a bitwise column because there may be more columns added in the future. With a bitwise column, you just add a new value. With bit columns you have to change the structure of the table which requires more oversight, upgrades, etc...
Relational databases are specifically optimized to work with a fixed database schema. Which means that, yes, as requirements change you have to change the schema, plan a migration, etc. (Adding columns is usually quite easy, as long as you don't fall for the trap of caring too much about columns order, and as long as you always avoid SELECT * and INSERT without column-list)
If your requirements are for a very flexible schema that can be changed at the spot, I would look at other technologies. XML, JSON, and several "NoSQL" database are optimized for storing so-called schema-free data.
December 13, 2016 at 10:46 am
Jack Corbett (12/13/2016)
Hey, long-time no hear from.Any chance you could normalize the structure so the Yes/No columns are in a separate or separate table(s). Maybe even an EAV table, which will eliminate the need to add columns later.
I'm not a big fan of bitwise columns because they aren't clear and, in my experience, aren't documented well enough for the next person who has to support it (yes, I have been the next person).
Putting all the bits in a separate table can, depending on other requirements, be a good idea.
Using an EAV table is probably a bad idea. That structure has perhaps even worse issues than the original bitmap suggestion as it comes to writing queries or enforcing constraints.
If documentation is your concern, then add documentation. Storing Y or N instead of a 1/0 bit increase storage requirements by a factor 8 (in a case like this where there are multiple bit columns); storing the words YES/NO fully bumps that to a factor 24. If there are just a few rows ... no problem. But if you do this on a very large table, it will have serious impact on storage size, but also on buffer pool usage and hence overall performance.
December 13, 2016 at 10:55 am
I see some strong advantages to the combined-bits approach.
To distinguish NULL values, I think you'd need another column of the same int type, with bits in it to indicate NULL or not. Viz:
DECLARE @test_table TABLE (
bit_values_null int,
bit_values int
)
INSERT INTO @test_table VALUES(0, 1)
INSERT INTO @test_table VALUES(1, 1)
INSERT INTO @test_table VALUES(0, 0)
SELECT bit_values_null, bit_values,
CASE
WHEN bit_values_null & 1 > 0
THEN 'bit 1 is NULL'
WHEN bit_values & 1 > 0
THEN 'bit 1 is 1'
ELSE 'bit 1 is 0' END
FROM @test_table
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 13, 2016 at 11:12 am
Jack Corbett (12/13/2016)
Hey, long-time no hear from.Any chance you could normalize the structure so the Yes/No columns are in a separate or separate table(s). Maybe even an EAV table, which will eliminate the need to add columns later.
I'm not a big fan of bitwise columns because they aren't clear and, in my experience, aren't documented well enough for the next person who has to support it (yes, I have been the next person).
Without knowing anything about your process, having a separate table seems to be good compromise between simplicity and schema stability.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 13, 2016 at 2:58 pm
Jack Corbett (12/13/2016)
Hey, long-time no hear from.Any chance you could normalize the structure so the Yes/No columns are in a separate or separate table(s). Maybe even an EAV table, which will eliminate the need to add columns later.
I'm not a big fan of bitwise columns because they aren't clear and, in my experience, aren't documented well enough for the next person who has to support it (yes, I have been the next person).
I'm trying to avoid an EAV table because it has been poorly implemented elsewhere in the database & I'm trying to fix that as well.
I'm planning on having a table with bit definitions that isn't linked back to the table. I want good, maintainable documentation for this.
It has been awhile, hope you're doing well.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 13, 2016 at 2:59 pm
Hugo Kornelis (12/13/2016)
Sioban Krzywicki (12/13/2016)
I want to use a bitwise column because there may be more columns added in the future. With a bitwise column, you just add a new value. With bit columns you have to change the structure of the table which requires more oversight, upgrades, etc...Relational databases are specifically optimized to work with a fixed database schema. Which means that, yes, as requirements change you have to change the schema, plan a migration, etc. (Adding columns is usually quite easy, as long as you don't fall for the trap of caring too much about columns order, and as long as you always avoid SELECT * and INSERT without column-list)
If your requirements are for a very flexible schema that can be changed at the spot, I would look at other technologies. XML, JSON, and several "NoSQL" database are optimized for storing so-called schema-free data.
Adding columns is quite easy technically, but it is a long business process with reviews and committees. I think a bitwise column makes sense here.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 13, 2016 at 3:00 pm
ScottPletcher (12/13/2016)
I see some strong advantages to the combined-bits approach.To distinguish NULL values, I think you'd need another column of the same int type, with bits in it to indicate NULL or not. Viz:
DECLARE @test_table TABLE (
bit_values_null int,
bit_values int
)
INSERT INTO @test_table VALUES(0, 1)
INSERT INTO @test_table VALUES(1, 1)
INSERT INTO @test_table VALUES(0, 0)
SELECT bit_values_null, bit_values,
CASE
WHEN bit_values_null & 1 > 0
THEN 'bit 1 is NULL'
WHEN bit_values & 1 > 0
THEN 'bit 1 is 1'
ELSE 'bit 1 is 0' END
FROM @test_table
Yeah, that was the direction I was heading. Thanks!
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 13, 2016 at 3:04 pm
You could also have another table that documented the bit value meanings. Here's a quick-and-dirty example, would obviously need fleshed out quite a bit more:
CREATE TABLE dbo.bit_values_master (
bit_value int,
description varchar(200),
is_null_valid bit,
is_on_valid bit,
is_off_valid bit
)
INSERT INTO dbo.bit_values VALUES(1, 'Is allowed to access this system?', 0, 1, 1)
INSERT INTO dbo.bit_values VALUES(2, 'Is allowed to print invoices?', 1, 1, 1)
INSERT INTO dbo.bit_values VALUES(4, '...', 1, 1, 1)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 13, 2016 at 4:03 pm
Sioban Krzywicki (12/13/2016)
I'm trying to avoid an EAV table because it has been poorly implemented elsewhere in the database & I'm trying to fix that as well.
Somebody implemented EAV poorly does not mean it should not be used at all.
Appalling quality of Lada's does not mean Toyota should stop producing cards.
I'm planning on having a table with bit definitions that isn't linked back to the table. I want good, maintainable documentation for this.
Any bit column must represent an available property or an event relevant to the entity.
"Approved" means there must be a record of approval even somewhere.
"EmailSent" means there must be a corresponding record in EmailLog.
"IsActive" means that the account, or whatever it is, did not pass its "End Date".
You better record those properties/events in a properly designed relational database, and use LEFT JOINs to figure out the bit value.
Otherwise you're gonna need to constantly update those bit values to bring them in sync with actual events happened to the entity.
And pretty soon you'll be posting questions "how do I avoid those horrible deadlocks"?
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply