SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    calendar / attendance table query setup

    I have a table with a field for every day of the year. They're all integer fields. The table also includes a integer field to track the employee id. And then there is also a field to track year. I then have another table that has the attendance types, ie. late, absent, holiday, etc. This table has a primary key field id for each item and then a field to describe the record.
    The problem I am having is when I want to display a full year on a php page. I believe the SQL to reproduce it is just too big. Maybe someone has some suggestions. In order to display the descriptions for every day(for a full year) I have to do a left join on every day in the SQL. That's 366 left joins. The page times out before it even comes up, even though there are only a couple records in the database so far. Is there a better structure for the tables and SQL?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, there sure is a better structure

    instead of 366 columns, you want 366 rows

    the key to the table should be employee id and date, not employee id and year

    for more information, do a search for first normal form
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •