How to handle dozens of flags in a database

Question

As with most applications, I have a "users" table that describes entities that can go into it. It has information like their alias, their email address, their salty password hashes, and all the usual candidates.

However, as my application grows, I need more and more special case flags, which I usually get stuck in the user table. For example, how their last monthly email message was sent, regardless of whether they rejected the pop-up, how many times they clicked on the “I'm awesome” button, etc.

I got quite a few of these fields, and most of these flags I do not need for most of the web pages that I process.

Is there anything wrong with keeping all these flags in the user table? Is there much better place to put them? Can the creation of other tables with a 1: 1 ratio with the user table provide additional overhead for data extraction when I need it?

In addition, I use Hibernate as my ORM, and I am worried that creating a bunch of additional tables for this information means that I will also have to pollute my user domain object. Advice?

Answer 1

There are several common solutions:

• Eav

  , . : , . . ( ).

• . . : . , . , , .

• BIT , "". "" . : ALTER TABLE ADD COLUMN. , , .

Answer 2

, :

create table users (
    id integer primary key,
    user varchar(32) unique
)

create table flags (
   id integer,
   flagname varchar(32),
   flagval char(1)
)

id + flagname. :

1, 'administrator', 'Y',
1, 'editor', 'Y',
2, 'editor' 'Y'

.. .

Answer 3

, - , .

, .

-, , - .

, : . "/" ( ): .

, , , , .. .. , .

" , BOOLEAN ( )". - C. J. , SQL (2009). "

, , .

, boolean ? (, , , ) , boolean , , ""?)

IOW, boolean, boolean, DML, ?

( ), " N , N > 1, > 1 , > 1 " > 1 , > 1 ".

EDIT

" :" ". , , "

.

, Date , , . . , , , , , , relvars, , .

, . . . , , , .

" ( ) " , / relvar ( !). / relvar , , . , , - ( ) (!), boolean. boolean , , proprosition , ?

Answer 4

, ? , null - .

( ):

select count(*) from AwesomeClicks where userid = 1234

userid ( , ).

select userid from DismissedTutorialPopup where userid = 1234

1234 ( ), ( ).

, CreateDate, , ..

Answer 5

, base64, varchar. (6 )

, , . . :

public class Flags
{
   public const string Flag1 = "1";
   public const string Flag2 = "2";
   public const string Flag3 = "4";
   public const string Flag4 = "8";
   public const string Flag5 = "g";
   public const string Flag6 = "w";
   public const string Flag7 = "10";
   // ... etc ...
}

Answer 6

**** - , ? ****

, , Db , SQL-, , 8060 . ( 8060).

.

SQLserver 2005 - 8060 bytes MYSQL - 8052 bytes Oracle 9i - 255000 bytes.