| Author |
Topic |
|
sql777
Constraint Violating Yak Guru
314 Posts |
 Posted - 2003-11-23 : 12:34:33
|
| Hi, Say I have an employee table that has to inform the application which days the employee works, namely Sunday, Monday, Tuesday, Wed, Thurs, Fri and Saturday.Option#1Have 7 table columns of type bit that I can easily keep track of which days the user worksOption#2Some sort of bit mask, if so, what datatype should I use. What are the drawbacks of this approach? I can forsee that queries would be a little more expensive with tricky WHERE clauses?TIA |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-11-23 : 14:23:48
|
| If you look in BOL, there are words saying that seven bit fields will be stored in a single byte. (Don't allow NULLS on the bits).The larger attraction of using a bitmask is the ability to check all 7 bits with a single compre. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-11-23 : 16:17:03
|
| How about a tableEmployeesID, daynumber==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
sql777
Constraint Violating Yak Guru
314 Posts |
Posted - 2003-11-23 : 18:02:16
|
| nah, I don't want to create 7 columns let alone another table!hehe..thanks. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-11-23 : 19:04:35
|
| You asked for a "best practice", Nigel gave you one and you turned it down.perhaps you should title your thread "nasty hack"Damian |
|
|
|
sql777
Constraint Violating Yak Guru
314 Posts |
Posted - 2003-11-24 : 13:36:50
|
| No I just don't feel that fits my situation. I understand where he is coming from though, I didn't mean to be rude to his suggestion (if that's how it came out).TIA |
|
|
|
mkbosmans
Starting Member
15 Posts |
Posted - 2003-11-24 : 15:50:18
|
| So, why don't you think Nigels approach is better?And what kind of calculations and querys will you be running against the fields? |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-11-24 : 19:01:50
|
| What if you want to store any attributes relating to these days? I.e., start time, end time, lunch break, etc? That is why when you normalize the schema and make each day 1 ROW in a new table, you can add more attributes to each day and you won't have redudant or hard to examine/query data.Instead of storing all days explicitly for each employee (which WOULD be hard to maintain and be lengthy) you could also store basic TEMPLATES which consist of a series of rows for which day that template applys to. then just assign each employee a template. i.e.,Templates Table:Template-------"M-F""Weekend Only""Sun-thu"TemplateWeekDays Table:Template, Weekday-------,--------"M-F",2"M-F",3"M-F",4"M-F",5"M-F",6"Weekend Only",1"Weekend Only",2"Sun-Thu",1"Sun-Thu",2"Sun-Thu",3"Sun-Thu",4"Sun-Thu",5..etc... something like that. Just some ideas to give you a different perspective on how to think about your problem.- Jeff |
|
|
|
sql777
Constraint Violating Yak Guru
314 Posts |
Posted - 2003-11-24 : 22:32:20
|
| true, but then my application has to make a seperate call to list the days for the employee. That is what I want to avoid.If all the data is in the 1 row, I don't need to make another db call to get the list of days. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-11-24 : 22:50:50
|
| You can still do it in one call. That's the whole reason for having all access via stored procedures - so the catabase structure is independent of the result sets returned.In this case you could do it in a single query if you wish whether or not the data is in a single table or in the normalised table I suggested.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-11-24 : 23:38:08
|
quote:
so the catabase structure is independent of the result sets returned.
Is a catabase like a cross between a database and a catalog ?I like it  Damian |
|
|
|
sql777
Constraint Violating Yak Guru
314 Posts |
Posted - 2003-11-25 : 00:17:21
|
| NR,Can you post the query, cuz I dont' believe you! *smile*You have a parent table, and a child table that contains 0 or more rows...how can you return it in 1 query? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-11-25 : 00:26:56
|
| To get the same result as holding in a single table a couple of waysselect e.employeeID, day1 = coalesce(ed1.daynumber/ed1.daynumber,0), day2 = coalesce(ed2.daynumber/ed2.daynumber,0), ... from employee e left outer join empday ed1 on e.employee_id = ed1.employee_id and ed1.daynumber = 1left outer join empday ed2 on e.employee_id = ed2.employee_id and ed2.daynumber = 2left outer join empday ed3 on e.employee_id = ed3.employee_id and ed3.daynumber = 3.....or something likeselect e.employee_id, day1 = sum(case when ed.daynumber = 1 then 1 else 0 end), day2 = sum(case when ed.daynumber = 2 then 1 else 0 end), ...from employee e left outer join empday ed on e.employee_id = ed.employee_idgroup by e.employee_id==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
best practise to handle this...