Modeling Data with the Days of the Week

viper2843 · #1 (**permalink**) 03-18-10, 22:33

I am modeling a set of data that is basically a set of boolean values for the days of the week. So for id 1 there will be a boolean value for each day of the week. I have thought of two ways to model this and I want to get some opinions and pros/cons for each method from people smarter than me.

The first method uses a field for the day of the week. The SQL examples are from PostgreSQL:

Code:

CREATE TABLE example_two (
    id INTEGER NOT NULL,
    day_of_week VARCHAR(10) NOT NULL,
    status BOOLEAN NOT NULL
);

The second has a column for each day of the week:

Code:

CREATE TABLE example_one (
	id INTEGER NOT NULL,
	sunday_status BOOLEAN NOT NULL,
	monday_status BOOLEAN NOT NULL,
	tuesday_status BOOLEAN NOT NULL,
	wednesday_status BOOLEAN NOT NULL,
	thursday_status BOOLEAN NOT NULL,
	friday_status BOOLEAN NOT NULL,
	saturday_status BOOLEAN NOT NULL
);

Are these the only way? Is there an even better way that I haven't thought of? Your feedback is much appreciated. Thanks.

r937 · #2 (**permalink**) 03-18-10, 23:52

how many total rows will there be in each of your two examples?

viper2843 · #3 (**permalink**) 03-19-10, 07:42

In the example with the day of week field, there would be 7 rows per id and the second example would have 1 row per id. I figure there will be about 500 ids to start with.

r937 · #4 (**permalink**) 03-19-10, 08:31

okay, that makes sense

can you give me some idea what the ids represent?

also, what do the booleans mean? i realize they are true/false, but what are they for?

blindman · #5 (**permalink**) 03-19-10, 11:02

A bitmask might also be appropriate, depending upon how you need to use the data and how adventurous you are feeling.

r937 · #6 (**permalink**) 03-19-10, 11:22

if by "adventurous" you mean "let's save a couple hunnert bytes of storage space while complexifying the SQL so that it becomes painful to write" then yeah, knock yourselbst out

mike_bike_kite · #7 (**permalink**) 03-19-10, 12:06

Quote:

Originally Posted by blindman

A bitmask might also be appropriate

I'm sure storing multiple bits of info in a single field goes against normal form as well

blindman · #8 (**permalink**) 03-19-10, 12:39

Not necessarily. It records a single "state" of the system, in a single field.

mike_bike_kite · #9 (**permalink**) 03-19-10, 13:56

Quote:

Originally Posted by blindman

Not necessarily. It records a single "state" of the system, in a single field.

If it's just a single state why would you need the bitwise operators to get at those little ones and noughts in the field? Isn't doing it that way just the binary equivalent of the following? Here it's obvious that the table isn't normalised but it's exactly the same as the binary single field but just done with a string - at least the SQL would be more readable than using the bitwise operators.
Code:
insert nasty_example ( id, days ) values (123, "Mon,Tue,Wed,Thur,Fri" )

Quote:

Originally Posted by viper2843

Is there an even better way that I haven't thought of? Your feedback is much appreciated.

You've showed us two solutions and asked us say which is best but wouldn't it be better to tell us what you are trying to model first.

dportas · #10 (**permalink**) 03-19-10, 16:08

Quote:

Originally Posted by mike_bike_kite

[INDENT]Here it's obvious that the table isn't normalised but it's exactly the same as the binary single field but just done with a string - at least the SQL would be more readable than using the bitwise operators.

Code:

insert nasty_example ( id, days ) values (123, "Mon,Tue,Wed,Thur,Fri" )

I don't agree that it's "not normalized". It's obviously a single value in the days column because SQL doesn't permit multi-valued columns or repeating groups. It may not be a very practical design but that's different from saying that it's not normalized.

I think that's an important distinction because in many cases it does make perfect sense to store "complex" values in columns: long strings, bitmaps or even whole documents, objects or files for example. It can make sense to do that even though they may need to be manipulated or exploded into sets of values later on.

The data management landscape is rich with "complex" types and values. If we make the arbitrary distinction that complex values somehow violate 1NF (which after all is nothing more or less than the definition of a relation itself) then that just perpetuates the myth that the data models we use are not capable of representing a lot of the information people are interested in today.

Back to the OP's question. One possible disadvantage of having each day in a different column is that it makes it harder to answer queries that need to aggregate across several different days of the week. I would also like to know why there are no actual dates in this model. If partular dates are being represented then that might change the picture again. It's nearly impossible to make well-founded and specific design recommendations without the opportunity to analyse the business scenario in detail.

blindman · #11 (**permalink**) 03-19-10, 16:21

Oh hell yes, a bitmask field would be much preferable to a delimited string.
Are you being serious?
You need to read up on bitmasks, I think.

viper2843 · #12 (**permalink**) 03-19-10, 22:16

Sorry for the delay. Didn't have any time at work to reply.

Thank you all for your interest and replies. I am trying to model whether or not a particular stock market is either open or closed. For example, the New York Stock exchange is open Monday - Friday but not Saturday or Sunday. The Tel Aviv Stock Exchange is open Sunday - Thursday but not Friday or Saturday. I thought that I could store whether or not the market was open as a boolean value and that led me to the two options I have above.

r937 · #13 (**permalink**) 03-19-10, 23:51

aha, stock markets (my guess was restaurants)

aside: there are 500 stock markets???

so, do you care about holiday closings? like for example christmas day?

mike_bike_kite · #14 (**permalink**) 03-20-10, 09:06

Quote:

Originally Posted by dportas

I don't agree that it's "not normalized". It's obviously a single value in the days column because SQL doesn't permit multi-valued columns or repeating groups. It may not be a very practical design but that's different from saying that it's not normalized.

I don't know if that is obvious. Here we're trying to store which days of the week a stock market is open in a single field. I'd say this is a repeating group and breaks 1NF ...
Let's take an Employee table where the designer has a repeating group of employee ids separated by commas to show who a person manages. Our designer stores this string as a single value in a field as follows using SQL
Code:
insert Table ( mgr_id, emp_ids ) values ( 12, "34,38,56,76,189" )
Let's now pretend that the designer felt embarrassed when a colleague laughed at his design pointing out the repeating group of ids separated by commas. He decides that it's the commas that make his design bad so he now uses fixed width employee numbers.
Code:
insert Table ( mgr_id, emp_ids ) values ( 12, "034038056076189" )
His colleague still didn't seem impressed and says the design is inefficient so our designer decides to cram the ids more tightly using 10 binary digits for each employee. He figures he can access any employee by just using bitwise operators.
Code:
insert Table ( mgr_id, emp_ids ) values ( 12, #1100010010101001010010101010101000001001010010111010101010101111100101010101001011010101010111110101010111110101010111010001111110001101111 )
I'm guessing you think the above approach is insane because you can't access the employees a person manages without resorting to chicanery with various shift and mask operators. So why do you say storing the individual flags for each day in a single binary field is any different? Is it because we're using one bit rather than ten? is it because the separate fields being squeezed into one field are binary rather than integers?
At the end of the day I guess it depends on what type of code you want to maintain. To find which markets were open on either Mon, Tue or Fri the code would be something like this for the two approaches:
Code:
select distinct id from MarketDays where day in ( "Mon", "Tue", "Fri" )

select id from Market where binaryDays && 98 > 0
The binary version certainly produces more compact code but it's a pain to read or maintain. Perhaps if we just wanted to secure our job into the next millennium then this might be the way forward

PS I don't think any of the methods so far is the right way to approach the problem.