Database Table design for Weekdays and Values

Question

Guys,

I would love to here your ideas on this. I am creating a table which will store weekly shift hours for employees.

for example: Jon works 9:00am to 9:00pm Monday , 10:00 AM to 5:00PM on Tuesday, etc.

How should I go on designing this table? I thought of 2 options.

(1) For every record I can have two lines for AM and PM and have columns for Monday to Sunday

ID | ParentID | Type | Monday | Tuseday ......Sunday
-----------------------------------------------------------------
1  | 1        | AM  | 9:00    | 9:00          12:00
2  | 1        | PM  | 9:00    | 5:00          02:00
3  | 2        | AM  | 10:00   | 
4  | 2        | PM  | 10:00   |

(2) I can save Preference in XML format in one column

ID | Info
-------------------------------------------------------------------
1  | |Hours|Monday|9:00 AM - 9:00PM|Monday|Tuseday........|Hours

Any better ideas ?

Thanks

Answer 1

Typically, if I was to design the database I would decide on wether the information is purely stored in the database for use in the application, or would there be queries written against this data.

I have found that storing XML data is a nice way of storing application data, that will be used by the application.

When I know that there will be queries written to these fields, it is more cumbersome using XML, So then I would rather use a normal table structure.

So, it would depend on what the use of this data will be, and if you intend on writing queries, creating reports from these fields.