Database table design for weekdays and values
I would like here your ideas on this matter. I am creating a table that will store the weekly hours for employees.
for example: John works from 9:00 am to 9:00 pm Monday, 10:00 am to 5:00 pm on Tuesday, etc.
How can I continue developing this table? I thought of two options.
(1) For each entry, I have two rows for AM and PM and 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 as XML in one column
ID | Info
-------------------------------------------------------------------
1 | |Hours|Monday|9:00 AM - 9:00PM|Monday|Tuseday........|Hours
Any better ideas?
thanks
a source to share
Typically, if I were to create a database, I would decide that the information would be stored in the database only for use in the application, or whether queries would be made against that data.
I have found that storing XML data is a good way to store application data to be used by the application.
When I know that queries will be written in these fields, it is more cumbersome using XML. Therefore, I would prefer to use the normal table structure.
As such, it will depend on what will use this data and if you intend to write queries generating reports from these fields.
a source to share