What is the best way to structure Days of the week in a db

This is a normalization thing, but I want I have to hold information about the days of the week. Where the user is go开发者_C百科ing to select each day and put a start time and a finish time. I need this info to be stored in a db. I can simply add 14 fields to the table and it will work (MondayStart,MondayFinish,TuesdayStart, etc). This doesnt seem

Do NOT design your database to match the UI.

My time keeping system at my job has a place to enter data for each day of the week. That doesn't mean you store it that way.

You need a table for users and one for times

User_T
  User_ID

Time_log_T
   User_ID
   Start_dt (datetime)
   End_dt (Datetime)

Everything can be derived from this.

If you want to have one check-in per day create a unique constraint on User_ID, TRUNC(start_DT). This will handle third shift that wrap days. RDBMS cannot express that the next start_dt for a given User_ID is > MAX(End_DT) for that user... you'll have to do that in code. Of course if you allow records from previous days to be entered or corrected you'll need to validate them to be non-overlapping in a more complex style.

Think of all the queries you'd throw at these tables; This will beat the 14 columns 99% of the time.

Users 
  id
  ...etc...

Days
  id
  day nvarchar (Monday, Tuesday, etc)
  start_time datetime
  end_time datetime
  user_id

you could also break out day in Days to a day of week to enforce consistency on the day if you only want to allow specific days or what not so Days would become

Days
  id
  day_of_week_id
  ...etc...

DaysOfWeek
  id
  name

I don't think moving the data to another table would accomplish anything. There would still be a one-to-one (main record to 14 fields) relationship. It would be more complex and run slower.

Your instincts are good but in this case I think you would be better off leaving the data in the table. Over-normalization is a bad thing.

You could create a table with 3 columns -- one for the day (this would be the primary key), one for the start time, and one for the finish time.

You would then have one row for each day of the week.

You could extend it with, say, a column for a user id, if you are storing the start and finish time for each user on each day (in this case, the primary key would be user id and day of the week)... or something similar to suit your needs.