UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Multiple Yes/no Fields, Any Version    
 
   
Jaiket
post Apr 15 2019, 11:36 AM
Post#1



Posts: 385
Joined: 3-May 17
From: France


Hello,
I saw a recent post on multiple Yes/No fields in one table. It was pointed that they are not usually normalised.
I thought bitwise operators were just a highly efficient way of doing multiple yes/no (boolean) fields. Is this true, and if so, what are/were they used for?
This post has been edited by Jaiket: Apr 15 2019, 11:39 AM

--------------------
Life is too short to drink bad wine.
Go to the top of the page
 
theDBguy
post Apr 15 2019, 11:44 AM
Post#2


Access Wiki and Forums Moderator
Posts: 75,577
Joined: 19-June 07
From: SunnySandyEggo


Hi. Using Yes/No fields typically means designing the data into the fields of a table, which is not really considered a good design. Usually, a Yes/No field can be designed without using a Boolean field. For example, one potential Yes/No field might be used to indicate if the record is currently active or not. If being "active" means an employee hasn't left the company yet, then simply looking for a missing or empty termination or departure date would be sufficient to determine if the record is active or not.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Jaiket
post Apr 15 2019, 11:45 AM
Post#3



Posts: 385
Joined: 3-May 17
From: France


Thanks DBGuy,
So is it ok to have multiple boolean fields in a table?
Ok, I need to read your answer and think about this.
This post has been edited by Jaiket: Apr 15 2019, 11:47 AM

--------------------
Life is too short to drink bad wine.
Go to the top of the page
 
theDBguy
post Apr 15 2019, 11:49 AM
Post#4


Access Wiki and Forums Moderator
Posts: 75,577
Joined: 19-June 07
From: SunnySandyEggo


It could be OK, but it depends on what they represent. If for example you have a bunch of yes/no field to indicate a series of attributes, then no, it's better to have them in a child table. For example, let's say you have a table of developers and decide to use yes/no fields like PHP, HTML, VBA, Java, C+, Perl, MySQL, etc. Meaning, if it's a yes on each field, the developer "knows" it or is certified on that particular skill. There's a better way to design that table.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Apr 15 2019, 11:50 AM
Post#5


UA Admin
Posts: 35,171
Joined: 20-June 02
From: Newcastle, WA


They are efficient for that kind of value, yes. That's not the same thing, though, as the discussion here.

Multiple Yes/No fields can be a red flag for non-normalized data, but that isn't quite the same thing.

Let's say, for example, you want to pick one of five kinds of service plans each customer purchases; "Minimal", "Good", "Better", "Best" or "Awesome". If you set that up as five yes/no fields, the result is "efficient" for each field, but horribly inefficient in the context of data integrity and usefulness.

For one thing, you have to write additional logic for the interface to control selection of one, and only one, of the five plans. Doable, but equivalent to paddling your canoe upstream against the current.

If you design that properly, as a single field which contains the foreign key for the plan selected, 1 through 5, the result is an interface closer in experience to floating downstream, only using your paddle to steer.

Also, by imposing referential integrity on that relationship, you let the database engine control what users can choose, limiting them to one and only one of the five approved plans and no others.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Jaiket
post Apr 15 2019, 12:19 PM
Post#6



Posts: 385
Joined: 3-May 17
From: France


Something is stuck in my head, too many questions.
I pasted them locally and I'll mull it over 'till tomorrow.
I do understand the examples, thanks.
This post has been edited by Jaiket: Apr 15 2019, 12:19 PM

--------------------
Life is too short to drink bad wine.
Go to the top of the page
 
theDBguy
post Apr 15 2019, 12:21 PM
Post#7


Access Wiki and Forums Moderator
Posts: 75,577
Joined: 19-June 07
From: SunnySandyEggo


Hi. If you have a specific example in mind, please post it, so we can say if we agree with it or not.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Jaiket
post Apr 15 2019, 01:17 PM
Post#8



Posts: 385
Joined: 3-May 17
From: France


You hit the nail on the head, theDBguy.
Its high time I stopped refactoring and theorizing and actually do something real.
This post has been edited by Jaiket: Apr 15 2019, 01:18 PM

--------------------
Life is too short to drink bad wine.
Go to the top of the page
 
MadPiet
post Apr 15 2019, 01:22 PM
Post#9



Posts: 3,136
Joined: 27-February 09



I have worked on databases with that kind of pattern. (Information stored in column names). I found two very serious problems with this design:
1. Completely inflexible design (what do you do if you forget a programming language, for example?) Do you add a new column?
2. have you ever tried writing a complicated query against something like this?

If you move the information stored in the column name to a field in the table, this suddenly becomes MUCH more flexible and easier to summarize. Might require slightly more complex queries, but in the long run, MUCH easier to use and answer more complex queries.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th June 2019 - 04:41 AM