By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,045 Members | 1,305 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,045 IT Pros & Developers. It's quick & easy.

table structure for seasonal pricing for members and guest

omerbutt
100+
P: 638
Hi all ,
i need to add pricing structure for bookings , what do you people suggest, minimum how much tables would be needed for this.
Expand|Select|Wrap|Line Numbers
  1.  
  2. Winter        
  3.                                       Member    Guest
  4.  
  5. Weekend                                  40         50
  6.  
  7. Weekday                                  30         40
  8.  
  9. Weekend Special                          60         75
  10.  
  11. Weekday special (sun  Thursday) 120 / 5 nights    200 / 5 nights
  12.  
  13.  
  14. Summer        
  15.  
  16. Weekend                                  30         35
  17.  
  18. Weekday                                  25         30
  19.  
  20. Weekend Special                          45         55
  21.  
  22. Weekday special (sun  Thursday)    100/ 5 nights     140 / 5 nights
  23.  
  24.  
regards,
Omer Aslam
Oct 28 '11 #1

✓ answered by omerbutt

well patjones i carried it out with 3 tables namely, hope you could suggest the best of the 2

jos_seasons(seasons listings)
jos_gbr(guest booking rates)
jos_mbr(member booing rates)
Expand|Select|Wrap|Line Numbers
  1. jos_seasons
  2. season_id     bigint(20) Primary     Index     
  3. season_name     varchar(255)
  4.  
  5. jos_gbr
  6. gbr_id             bigint(20) auto_increment     Primary    gbr_weekday     decimal(19,2)             
  7. gbr_weekend     decimal(19,2)             
  8. gbr_weekday_sp     decimal(19,2)             
  9. gbr_weekend_sp     decimal(19,2)             
  10. season_id     int(20)
  11.  
  12. jos_mbr
  13.  
  14. mbr_id     bigint(20)             
  15. mbr_weekday     decimal(19,2)             
  16. mbr_weekend     decimal(19,2)             
  17. mbr_weekday_sp     decimal(19,2)             
  18. mbr_weekend_sp     decimal(19,2)             
  19. season_id     bigint(20)
  20.  
  21.  

Share this Question
Share on Google+
4 Replies


patjones
Expert 100+
P: 931
Hi omer,

I would keep it in one table, like this:

Expand|Select|Wrap|Line Numbers
  1. fldSeasonID fldCost   fldSeason
  2.  
  3. 1            50.00     Winter Weekend (Guest)           
  4. 2            40.00     Winter Weekend (Member)
  5. 3            40.00     Winter Weekday (Guest)
  6. ...          ...       ...
  7. 16          140.00     Summer Weekday Special (Guest)
Oct 31 '11 #2

omerbutt
100+
P: 638
well patjones i carried it out with 3 tables namely, hope you could suggest the best of the 2

jos_seasons(seasons listings)
jos_gbr(guest booking rates)
jos_mbr(member booing rates)
Expand|Select|Wrap|Line Numbers
  1. jos_seasons
  2. season_id     bigint(20) Primary     Index     
  3. season_name     varchar(255)
  4.  
  5. jos_gbr
  6. gbr_id             bigint(20) auto_increment     Primary    gbr_weekday     decimal(19,2)             
  7. gbr_weekend     decimal(19,2)             
  8. gbr_weekday_sp     decimal(19,2)             
  9. gbr_weekend_sp     decimal(19,2)             
  10. season_id     int(20)
  11.  
  12. jos_mbr
  13.  
  14. mbr_id     bigint(20)             
  15. mbr_weekday     decimal(19,2)             
  16. mbr_weekend     decimal(19,2)             
  17. mbr_weekday_sp     decimal(19,2)             
  18. mbr_weekend_sp     decimal(19,2)             
  19. season_id     bigint(20)
  20.  
  21.  
Nov 2 '11 #3

patjones
Expert 100+
P: 931
Hi Omer,

Actually...let me think this one over a little. I want to make sure you get the correct normalization. I'll get back to you shortly.

Pat
Nov 2 '11 #4

patjones
Expert 100+
P: 931
What do you think about this structure? (I used SQL Server data types, by the way - so you would need to modify those for MySQL). You have three look-up tables like this:

Expand|Select|Wrap|Line Numbers
  1. tblRateType
  2.  
  3. fldRateID (tinyint)     fldRateType (varchar(75))
  4.  
  5. 1                       Weekend
  6. 2                       Weekday
  7. 3                       Weekend Special
  8. 4                       Weekday Special
Expand|Select|Wrap|Line Numbers
  1. tblUserType
  2.  
  3. fldUserTypeID (tinyint)    fldUserType (varchar(10))
  4.  
  5. 1                          Member
  6. 2                          Guest
  7.  
Expand|Select|Wrap|Line Numbers
  1. tblSeason
  2.  
  3. fldSeasonID (tinyint)     fldSeason (varchar(10))
  4.  
  5.  1                        Winter
  6.  2                        Summer

Then you have a main rate table:

Expand|Select|Wrap|Line Numbers
  1. tblRates
  2.  
  3. fldSeasonID  fldPatronType  fldDayType  fldRate
  4.  
  5. 1            1              1            40
  6. 1            1              2            30
  7. 1            2              3            75
  8. ...          ...            ...          ...
Nov 2 '11 #5

Post your reply

Sign in to post your reply or Sign up for a free account.