Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Posts
    57

    which one is better?

    hi,

    i am now designing a table to store member point by month.

    some ppl suggest my table like this

    MemberNo,
    Year,
    JanPoint,
    FebPoint,
    MarchPoint,
    AprilPoint,
    MayPoint,
    JunePoint,
    JulyPoint,
    AugustPoint,
    OctPoint,
    NovPoint,
    DecPoint

    and my idea is
    MemberNo,
    PointPeriod, (Year+Month)
    PointEarn

    the reason for first design is to avoid too many row (as we hv about 50,000 member)
    and the reson for second design is easy for data analysis and calculate expiry point.

    Any suggestion or advice on this?

    Thanks in advance!

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I believe your method is better because it allows easy reporting and it's more flexible. It would also allow you to aggregate historic data into fewer records ie put all the member points for the year into one record rather than 12 after 5 years say. If at some point in the future you want to store member points by the week rather than by the month then your method will still work while the other method makes this impossible.

    Their method is likely to be marginally quicker on the reporting side as it will pull back 1/12 the number of rows but this has to be offset against the more complex code that's required.

    Normally you'd have a from_date and a to_date which would allow any type of period to be stored - this would allow you to group all the member points for a year into one record.

    Mike

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Following on from Mikes suggestion - I reckon I'd do it this way.
    Members(MemberID, FName, SName, DOB, etc)
    Points(MemberID, DateFrom, DateTo, PointsEarnt)

    Where primary keys are underlined, foreign keys are in italics. This means we have a composite key on 3 fields in the Points table
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Thread moved to Database Concepts & Design
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I concur - your method is a better design (first normal form justifies it alone). Apart from the flexibility issue - you can't just measure size on the basis of the # of rows since your design ensures much narrower rows. I would imagine you would not record zero points for a month in your design. If someone earns no points from feburary onwards then your design is actually more efficient with regards to table size. But this is secondary to the other points raised above.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jun 2004
    Posts
    57
    very appreciate for all suggestion and advice, i really learn much from you all.
    Thank you.

Posting Permissions

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