How you arrange your data is a balance between two things:
(a) Storing the data, which is all about efficiency, consistency and integrity, and
(b) Using the data once you've stored it.
There's no point coming up with the most space-efficient storage model ever, if it then takes three weeks to retrieve two rows out of it.
Quote
For instance I need to store if a stock is up for the day, so I have upDay and downDay, each has a tinyint to store a 1/0. Is it good practice to break it out into upDay/downDay or just have upDownDay as a bool?
Does the [same] record hold the "start" and "end" values for that stock on that day? If so, you might consider
not storing the change at all, but calculating it as required [in a view] based on the underlying values in each row.
select start_value
, end_value
, end_value - start_value as increase
from table1
...
Quote
I have inBase, inBuyZone, inSellZone all relate to where the stock is in the cycle, there will only be one that is true ...
That sounds like a single field that can have one of a number of values. How you define those values is another choice; personally, I'd go with the sledgehammer, "look-up" table, solution.
create table StockStates
( id integer not null auto_increment
, name varchar( 40 )
, primary key ( id )
);
insert into StockStates ( name )
values ( 'inBase' )
, ( 'inBuyZone' )
, ( 'inSellZone' ) ;
create table stockTable6
( ...
, stockStateId integer not null
...
, foreign key stockStateId references StockStates( id )
...
If you ever need to add a new state, it's a simple, "insert" statement (that could be done through your application).
You
could use an Enum type, but adding a new value to one of those requires a change to the table
structure, which would (should!) require a Database Administrator (i.e. you) to get involved. That could get tedious if you have a lot of these types of field.
Regards, Phill W.