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
