You may find the SET column type of use here.
http://www.mysql.com/doc/en/SET.html
You could declare column Quarter as
Quarter SET ("one", "two", "three", "four") NOT NULL
or even
Quarter SET ("1", "2", "3", "4") NOT NULL
Then mysql will do some of the work for you.
Michael
Meli Meli wrote:
Thanks to everybody for helping me.
I agree with the suggestion to change the quarter fields in to one single field. The suggestion from Dan Greene to store the quarters as binary values sounds good to me. So I will do it this way.
To explain you more about this table. The quarter fields are representing 8 hours of a working day. Each quarter represents 2 hours. First quarter represents first 2 hours and so on.. I need to register activities made during the day and if there is an activitie, I need to register in which quarter of the day. So there made be no activity ore one or to etc.
The year, month, week, and day fields are actually year, cycle, week and day. This fields are not representing exact dates because the year is divided in to cycles. So one year has 8 cycles and one cycle has 6 weeks But also these fields I will combine to one data field and store a binary value. The table finally looks like:
Id
Quarter ->(binary value)
Eventdate ->(binary value)
Timestamp
Comments
That’s it!
Thanks an regards
Martin
Dan Greene <[EMAIL PROTECTED]> wrote:
I think that I must be missing something, as I agree with all the suggestions that to change the seperate date element columns to a single date field, but Meli's original post had a date falling into multiple quarters. Now to my knowledge, a date can only be in one quarter, from a calendar point of view, so maybe there's something more to Meli's issue...
to store the info more efficiently for what you're saying, you could also use binary as a guide
1 2 3 4 s n r t t d d h _______
8 4 2 1
- - - - 1000 = 8
0100 = 4
0010 = 2
0001 = 1
1100 = 12
1010 = 10
1001 = 9
0110 = 6
0101 = 5
0011 = 3
1110 = 14
1101 = 13
1011 = 11
0111 = 7
1111 = 15
0000 = 0 (which you don't have below but here for completeness)
and store a single number that represents the pattern you have below, replacing 'null' with 0 and x as 1
x null null null null x null null null null x null null null null x x x null null x null x null x null null x null x x null null x null x null null x x x x x null x x null x x null x x null x x x x x x x
Ladies and Gentlemen, the first real use of the bitwise section of the java certification exam I have ever used!!!!!!!!
-----Original Message----- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 9:01 AM To: Meli Meli Cc: [EMAIL PROTECTED] Subject: Re: Database-design
Why would you created separate fields for each quarter? Create a field called quarter and store a number in it. You could also combine year, month and day into a date field, which would make it easier to search on ranges.
So, I think your table should look like this:
id
quarter
eventdate
week
On Tuesday, November 11, 2003, at 02:33 PM, Meli Meli wrote:
I have a table with following structure:
Id first quarter second quarter third quarter last quarter year month week day
On an entry not all fields of the four quarter fields are
covered with
values. Following combinations are possible:
first quarter | second quarter | third quarter | last quarter
x null null null
null x null null
null null x null
null null null x
x x null null
x null x null
x null null x
null x x null
null x null x
null null x x
x x x null
x x null x
x null x x
null x x x
x x x x
The table will receive many thousands of entry's.
Would it be better to divide the table in to 15 small
tables in order
to not register fields with null values?
Thanks for helping
Regards Martin
--------------------------------- Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard
-- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--------------------------------- Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
