Wednesday, March 7, 2012

How to store matrix value in database

mo tu we th fr
_______________________
morning |___|____|_____|__X__|____|
afternoon |___|_X__|_X__|__X__|____|
evening |___|____|_X __|__X__|____|

Hey guys, i have to store the values of the above table into the database however i have know idea how to design the table. Could you guys give me some suggestion.Currently i come up with the below solution.

OpenTime
-----
OpenTimeID
DateID
TimeID

Date
----
DateID
DateName

Time
----
TimeID
TimeName

I prefer to use 1 table in this case, check this table:

CREATE TABLE testDate (id int identity(1,1),TDate datetime default getdate(),
WeekDay AS DATENAME(dw,TDate),
TimeRange AS CASE WHEN DATEPART(hh,TDate) BETWEEN 4 AND 12
THEN 'Morning'
WHEN DATEPART(hh,TDate) BETWEEN 13 AND 18
THEN 'Afternoon'
ELSE 'Evening'
END,
Value varchar(100))

INSERT INTO testDate(Value) SElECT 'This is a test record'

SELECT * FROM testDate

Hope this helpsSmile

No comments:

Post a Comment