Like the majority of applications, mine includes a "customers" table that describes the organizations that may sign in into it. It's info similar to their alias, their current email address, their salted password hashes, and all sorts of the typical candidates.
However, as my application is continuing to grow, I have needed increasingly more special situation "flags" that I have generally just stuck within the customers table. Things like whether their newest monthly email continues to be sent yet, whether they have ignored the tutorial popup, the number of occasions they clicked on the "I'm awesome" button, etc.
I'm starting to have a number of of those fields, and quite a few of those flags I do not need for almost all the web pages which i handle.
Can there be anything wrong with keeping many of these flags within the customers table? Can there be somewhere easier to place them? Would creating other tables having a 1:1 relationship using the customers table provide additional overhead to locating the information after i do require it?
Also, I personally use Hibernate as my ORM, and that i worry that producing a lot of extra tables with this information implies that I'd also need to dirty up my User domain object. Advice?
You will find several common solutions:
Store one flag per row inside a child table, having a mention of user row, the title from the flag, and also the value. Disadvantages: Can't guarantee a row is available for every flag. Have to define another research table for flag names. Reconstituting a person record with all of its flags is an extremely pricey query (takes a join per flag).
Store one flag per bit in one lengthy binary column. Use bitmasking in application code to interpret the flags. Disadvantages: Artificial limit on quantity of flags. Difficult to drop a flag if this becomes obsolete. Harder to alter flag values, look for specific flag values, or aggregate according to flag values without turning to confusing bitwise operators.
BITcolumn per flag, all within the Customers table. Most "correct" design in the outlook during relational theory and normalization. Disadvantages: Adding a flag requires
ALTER TABLE ADD COLUMN. Also, you may exceed the amount of posts or row size based on your make of RDBMS.
I'd say a much better design was something similar to this:
create table users ( id integer primary key, user varchar(32) unique ) create table flags ( id integer, flagname varchar(32), flagval char(1) )
witha primary key of id + flagname. flags records take a look like:
1, 'administrator', 'Y', 1, 'editor', 'Y', 2, 'editor' 'Y'
and so forth. I'd produce a view to gain access to the became a member of tables.