Hey Gang, I''m building a web app which allows our staff to enter their daily timesheets into a form which our HR department can then view/print etc. I''m not really sure what is the best way to structure the data for the bet efficiency. I already have a table called staffmembers with a unique id for each staffmember etc. Do I create another table/model/controller called timesheets, which is indexed to the staffmembers table, and inside the timesheets table have a row for each staffmember and day of the year? that doesnt seem terribly efficient, as then if I had 50 staff, I''d need 50*365 rows (18,350) in the table, which would make my app slow when it came to sorting timesheets (select * from timesheets where staff_id = n) for an individual or even a team of users wouldn''t it? Or is there a better way to do this kind of thing?
I would highly suggest that you buy a good book on relational database design. Your initial thoughts are the best way to attempt to solve this problem however, it would be a good idea to read some info and understand why this is the best design solution for your problem. BTW, 18,000 rows is absolutely tiny when it comes to database size and, if indexed correctly, an average machine could easily handle many hundreds of queries like the one you have below every second. -----Original Message----- From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Robbie Shepherd Sent: Friday, 24 June 2005 5:55 PM To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org Subject: [Rails] table structure best method? Hey Gang, I''m building a web app which allows our staff to enter their daily timesheets into a form which our HR department can then view/print etc. I''m not really sure what is the best way to structure the data for the bet efficiency. I already have a table called staffmembers with a unique id for each staffmember etc. Do I create another table/model/controller called timesheets, which is indexed to the staffmembers table, and inside the timesheets table have a row for each staffmember and day of the year? that doesnt seem terribly efficient, as then if I had 50 staff, I''d need 50*365 rows (18,350) in the table, which would make my app slow when it came to sorting timesheets (select * from timesheets where staff_id = n) for an individual or even a team of users wouldn''t it? Or is there a better way to do this kind of thing? _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Hi Robbie! You definitely have the general idea about how to solve this but you need to spend a little time reading/studying relational database design! ;-) You can get plenty of info on the web. Just googling for "timesheet relational modelling" I got to this URL in which you have an example of a similar thing of what you are trying to do: http://www.phptr.com/articles/article.asp?p=101586&seqNum=4&rl=1 "Relational Model" at the Wikipedia: http://en.wikipedia.org/wiki/Relational_model Just try to get the main concepts straight first: primary keys, foreign keys, index, ... Furthermore, 20K rows of data in a table is not that much!! That''s what indexes are for anyway! Don''t try to get all the theory straight from the begining either! Start coding and implementing soon and you will learn as you go. Good luck! /Rafa Robbie Shepherd wrote:> Hey Gang, > > I''m building a web app which allows our staff to enter their daily > timesheets into a form which our HR department can then view/print > etc. > > I''m not really sure what is the best way to structure the data for the > bet efficiency. > > I already have a table called staffmembers with a unique id for each > staffmember etc. > > Do I create another table/model/controller called timesheets, which is > indexed to the staffmembers table, and inside the timesheets table > have a row for each staffmember and day of the year? that doesnt seem > terribly efficient, as then if I had 50 staff, I''d need 50*365 rows > (18,350) in the table, which would make my app slow when it came to > sorting timesheets (select * from timesheets where staff_id = n) for > an individual or even a team of users wouldn''t it? > > Or is there a better way to do this kind of thing?
On 24-Jun-05, at 12:54 AM, Robbie Shepherd wrote:> Do I create another table/model/controller called timesheets, which is > indexed to the staffmembers table, and inside the timesheets table > have a row for each staffmember and day of the year? that doesnt seem > terribly efficient, as then if I had 50 staff, I''d need 50*365 rows > (18,350) in the table, which would make my app slow when it came to > sorting timesheets (select * from timesheets where staff_id = n) for > an individual or even a team of users wouldn''t it? > > Or is there a better way to do this kind of thing?Yes there is a better way to do this kind of thing - stop speculating and try it out. (I mean this in the nicest possible way) You''re allowing yourself to get sucked into the "balancing your hopes and fears" thing. The easiest way to get out of that mindset is to gather empirical data. Build a table as you''ve described and populate it with a years worth (personally I''d do a large multiple of that) of random sample data and do some performance tests. It''s probably the only way you''ll *really* be able to silence the nagging doubts in your head. HTH, Trevor