Hi there, I'm not sure if this the appropriate group so apologies if
it lies outside the boundary.
Senario: I have a customer table with contains a bunch of different
bit values that represent true/false values pertaining to the
customer. I decided for the purpose of clarity I would move these
values into another table. This way I would keep the customer details
(name, age, etc) separate from these values.
Question: Is this a good idea? Or could I somehow tally up all these
bit values and store it in one field in the customer table?
The application is a website and it is built using ASP.NET (VB.NET)
Any opinions would be great.
Cheers,
Jack 7 5634
On 18 Jan 2005 17:06:28 -0800, Jack wrote: Hi there, I'm not sure if this the appropriate group so apologies if it lies outside the boundary.
Senario: I have a customer table with contains a bunch of different bit values that represent true/false values pertaining to the customer. I decided for the purpose of clarity I would move these values into another table. This way I would keep the customer details (name, age, etc) separate from these values.
Question: Is this a good idea? Or could I somehow tally up all these bit values and store it in one field in the customer table?
The application is a website and it is built using ASP.NET (VB.NET)
Any opinions would be great.
Hi Jack,
I'd say: replace the bit columns with somewhat more descriptive columns,
like
ColName CHAR(1) NOT NULL CHECK (ColName IN ('Y', 'N'))
I don't see any advantage in moving these columns to a seperate table,
unless you are approaching the max number of columns per table (quite
unlikely) or unless you need to store these yes/no values only for a
limited subset of your customers.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
"Hugo Kornelis" <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message
news:r8******** *************** *********@4ax.c om... On 18 Jan 2005 17:06:28 -0800, Jack wrote:
Hi there, I'm not sure if this the appropriate group so apologies if it lies outside the boundary.
Senario: I have a customer table with contains a bunch of different bit values that represent true/false values pertaining to the customer. I decided for the purpose of clarity I would move these values into another table. This way I would keep the customer details (name, age, etc) separate from these values.
Question: Is this a good idea? Or could I somehow tally up all these bit values and store it in one field in the customer table?
The application is a website and it is built using ASP.NET (VB.NET)
Any opinions would be great.
Hi Jack,
I'd say: replace the bit columns with somewhat more descriptive columns, like ColName CHAR(1) NOT NULL CHECK (ColName IN ('Y', 'N'))
I don't see any advantage in moving these columns to a seperate table, unless you are approaching the max number of columns per table (quite unlikely) or unless you need to store these yes/no values only for a limited subset of your customers.
Best, Hugo --
(Remove _NO_ and _SPAM_ to get my e-mail address)
Another possible beneficial thing you can do with a 'side-car' table like
this.
Save only one row per distinct combonation of flags and store the integer
key to that bitmap pattern in the main table.
With an indexed column you would be able to more quickly select on those
values.
FamilyType Table
---------------
FamilyType Married BothWork HaveKids Descripiotn
----------------------------------------------------------------
1 Y Y N DINK
2 Y N Y That Type
3 N Y N Whatever
etc...
customer Table
----------
CustomerKey Name FamilyType
-------------------------------
1 joe and linda 1
2 bob and rob 3
3 karl and cindy 2
You can use a view, a proc, or an instead of trigger to make sure that
instead of updating the flags, you simply select the correct family type
code.
Hugo Kornelis (hugo@pe_NO_rFa ct.in_SPAM_fo) writes: I'd say: replace the bit columns with somewhat more descriptive columns, like ColName CHAR(1) NOT NULL CHECK (ColName IN ('Y', 'N'))
What can be more descriptive that 0 and 1?
Using character values when there are perfectly usable numeric values
as there are for binary value is asking for trouble. Let's see was
Y/N, T/F, J/N or something else?
We once had a lot of such columns in our database, but almost all are
bit columns these days. And, no, while the name of the type is ab_yesno,
the values are not Y/N, but J/N.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Jack (ja****@humlog. com) writes: Hi there, I'm not sure if this the appropriate group so apologies if it lies outside the boundary.
Senario: I have a customer table with contains a bunch of different bit values that represent true/false values pertaining to the customer. I decided for the purpose of clarity I would move these values into another table. This way I would keep the customer details (name, age, etc) separate from these values.
Question: Is this a good idea? Or could I somehow tally up all these bit values and store it in one field in the customer table?
The last thing would be a bad idea. That's more cumbersome to use, and
more prone to errors.
As for shuffling the bit columns to another table, well, it depends. We
actually did this with our accounts table, since we found that we were
costantly adding bit columns to it, of which some were for tiny marginal
features that were only referred to in one or two places.
Therefore we move these less used bit columns to this table:
CREATE TABLE accountflags (accountno int NOT NULL,
flag varchar(15) NOT NULL,
CONSTRAINT pk_flags PRIMARY KEY (accountno, flag))
That is, the bit column became rows. And a flag is set for an account
if there is a row in the table, else not. The flag themselves are
defined in another table, one that is loaded with data from scripts
when the database is created.
But if all you want to do is move bit columns to a side table, but
keep them as columns, I'm not sure that it's worth the effort.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
machine level things like a BIT or BYTE datatype have no place in a
high level language like SQL. SQL is a high level language; it is
abstract and defined without regard to PHYSICAL implementation. This
basic principle of data modeling is called data abstraction.
SQL is a high level language; it is abstract and defined without regard
to PHYSICAL implementation. This basic principle of data modeling is
called data abstraction.
Bits and Bytes are the <i>lowest<i> units of hardware-specific,
physical implementation you can get. Are you on a high-end or low-end
machine? Does the machine have 8, 16, 32, 64, or 128 bit words? Twos
complement or ones complement math? Hey, the standards allow decimal
machines, so bits do not exist at all! What about NULLs? To be a SQL
datatype, you have to have NULLs, so what is a NULL bit? By definition
a bit, is on or off and has no NULL. If you vendor adds NULLs to bit,
how are the bit-wise operations defined? Oh what a tangled web we
weave when first we mix logical and physical :)
What does the implementation of the host languages do with bits? Did
you know that +1, +0, -0 and -1 are all used for BOOLEANs, but not
consistently? In C#, Boolean values are 0/1 for FALSE/TRUE, while
VB.NET has boolean values of 0/-1 for FALSE/TRUE and they are
proprietary languages from the same vendor. That means <i>all<i> the
host languages -- present, future and not-yet-defined -- can be
different. Surely, no good programmer would ever write non-portable
code by getting to such a low level as bit fiddling!!
There are usually two situations in practice. Either the bits are
individual attributes or they are used as a vector to represent a
single attribute. In the case of a single attribute, the encoding is
limited to two values, which do not port to host languages or other
SQLs, cannot be easily understood by an end user, and which cannot be
expanded.
In the second case what some Newbies, who are still thinking in terms
of second and third generation programming languages or even punch
cards, do is build a vector for a series of "yes/no" status codes,
failing to see the status vector as a single attribute. Did you ever
play the children's game "20 Questions" when you were young?
Imagine you have six components for a loan approval, so you allocate
bits in your second generation model of the world. You have 64 possible
vectors, but only 5 of them are valid (i.e. you cannot be rejected for
bankruptcy and still have good credit). For your data integrity, you
can:
1) Ignore the problem. This is actually what <i>most<i> newbies do.
2) Write elaborate CHECK() constraints with user defined functions or
proprietary bit level library functions that cannot port and that run
like cold glue.
Now we add a 7-th condition to the vector -- which end does it go on?
Why? How did you get it in the right place on all the possible
hardware that it will ever use? Did all the code that references a bit
in a word by its position do it right after the change?
You need to sit down and think about how to design an encoding of the
data that is high level, general enough to expand, abstract and
portable. For example, is that loan approval a hierarchical code?
concatenation code? vector code? etc? Did you provide codes for
unknown, missing and N/A values? It is not easy to design such things!
--CELKO-- (jc*******@eart hlink.net) writes: machine level things like a BIT or BYTE datatype have no place in a high level language like SQL. SQL is a high level language; it is abstract and defined without regard to PHYSICAL implementation. This basic principle of data modeling is called data abstraction.
Completely wrong. In some language you can say "String" and the language
will allocate for you. In SQL Server you have to specify the max length
of any character column you want to use, and you cannot have more than
8000 bytes on a page. (There is varchar(MAX) in SQL 2005, but practice
will remain to specify an upper bound.)
As a further example, consider the whole range of tinyint, smallint,
int and bigint.
Since you store data on disk, the actual storage format matters a whole
lot to database programmers. Too much storage has bad effect on performance.
And of course, as datatype, BIT has nothing machine-level at all. It is
just a name for something that can have two values. While it could have
been called Boolean, Celko, Pamela or Urban, the name bit is quite good,
because most people have an understanding of how much information you
can cram into a bit.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
> Surely, no good programmer would ever write non-portable code by getting to such a low level as bit fiddling!!
There's nothing wrong with fiddling with your bits is there ? :-) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: G Kannan |
last post by:
Hey all!
I have written a perl script to retrieve information from a HTML Form
and insert the data into an Oracle database table. I am gettting the
the following error message:
"Use of uninitialized value in concatenation (.) at register.pl line
38, <STDIN> line 10."
The PERL code is as follows:
|
by: otto |
last post by:
i need to read a variable in a javascript and translate it to a form in
html
the javascript variable is:
<SCRIPT LANGUAGE='JavaScript'>RF2N('Total');</script>
and i need to put that variable as the value in this line
<input type="hidden" name="AMT" value="**">
|
by: Eric Chang |
last post by:
I was working on this simple form with radio boxes.
And when I click on one of the radio box, it tell me the value is
"undefined"
Why is that ? I did defined the value of each radio box:
<input type=radio name='Usetax' value='basic'
onClick='document.myform.amount.value=document.myform.Usetax.value'>
<input type=radio name='Usetax' value='no'
onClick='document.myform.amount.value=document.myform.Usetax.value'>
<input type=radio...
|
by: cwizard |
last post by:
I'm calling on a function from within this form, and there are values set
but every time it gets called I get slammed with a run time error...
document.frmKitAmount.txtTotalKitValue is null or not an object... the
function is like so:
function calc_total()
{
var x,i,base,margin,total,newmargin,newtotal;
base = document.frmKitAmount.txtTotalKitValue.value;
margin = document.frmKitAmount.margin.value/100;
|
by: dmiller23462 |
last post by:
So here's my problem.....I need to set up different email
distributions based on which option in the following Select form has
been chosen....For instance if "Putaway" is chosen it needs to email
User1@here.whatever and User4@here.whatever but if "Loaded" is chosen
it needs to email User2@here.whatever and User3@here.whatever, etc,
etc...
I'm aware that the only thing that really needs to change is the
"Mail.AddAddress" line (at least...
| |
by: matthew_carver |
last post by:
Hello,
I have an ASP page that loops through a SQL Server 2000 table, then
downloads an Excel sheet the users can save, etc. Works fine, except, I
see that in one particular "comments" field the Excel sheet returns a
#VALUE! error in the cell when there is a large amount of text. I've
looked through the MSKB, MSDN and many ng posts to see if there is a
workaround or solution to this, including looking at the xlWorksheet
properties. Is...
|
by: dbuchanan |
last post by:
Hello,
Here is the error message;
----------------------------
Exception Message:
ForeignKeyConstraint Lkp_tbl040Cmpt_lkp302SensorType requires the child
key values (5) to exist in the parent table.
----------------------------
|
by: tania |
last post by:
i have this table in my database:
CREATE TABLE FILM(
F_ID INT(5) NOT NULL AUTO_INCREMENT,
F_TITLE VARCHAR(40) NOT NULL,
DIRECTOR_FNAME VARCHAR(20) NOT NULL,
DIRECTOR_LNAME VARCHAR(20) NOT NULL,
TYPE VARCHAR(30) NOT NULL,
DURATION TIME ,
YEAR_RELEASE YEAR NOT NULL,
DESCRIPTION TEXT,
|
by: cbellew |
last post by:
Hi guys,
I have a problem with an option group and a two corresponding text boxes. When the user chooses an option value i want the text boxes to populate with text dependent on the choice made.
I have written an AfterUpdate procedure on the frame to try and assign the text to the value of the text box, but for some reason it does nothing...
Here is the code:
Private Sub Frame0_AfterUpdate()
If Frame0.Value = 1 Then
|
by: Astley Le Jasper |
last post by:
Sorry for the numpty question ...
How do you find the reference name of an object?
So if i have this
bob = modulename.objectname()
how do i find that the name is 'bob'
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |