table structure for seasonal pricing for members and guest

[omerbutt]

Hi all ,
i need to add pricing structure for bookings , what do you people suggest, minimum how much tables would be needed for this.

Winter		
		                              Member	Guest

Weekend	                              40	     50

Weekday	                              30	     40

Weekend Special	                      60	     75

Weekday special (sun – Thursday) 120 / 5 nights	200 / 5 nights
		
		
Summer		

Weekend	                              30	     35

Weekday	                              25	     30

Weekend Special	                      45	     55

Weekday special (sun – Thursday)	100/ 5 nights	 140 / 5 nights

regards,
Omer Aslam

[patjones]

Hi omer,

I would keep it in one table, like this:

[B]fldSeasonID fldCost   fldSeason[/B]

1            50.00     Winter Weekend (Guest)           
2            40.00     Winter Weekend (Member)
3            40.00     Winter Weekday (Guest)
...          ...       ...
16          140.00     Summer Weekday Special (Guest)

[omerbutt]

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

jos_seasons(sea sons listings)
jos_gbr(guest booking rates)
jos_mbr(member booing rates)

jos_seasons
season_id 	bigint(20) Primary 	Index 	
season_name 	varchar(255)

jos_gbr
gbr_id 	        bigint(20) auto_increment 	Primary	gbr_weekday 	decimal(19,2) 			
gbr_weekend 	decimal(19,2) 			
gbr_weekday_sp 	decimal(19,2) 			
gbr_weekend_sp 	decimal(19,2) 			
season_id 	int(20)

jos_mbr

mbr_id 	bigint(20) 			
mbr_weekday 	decimal(19,2) 			
mbr_weekend 	decimal(19,2) 			
mbr_weekday_sp 	decimal(19,2) 			
mbr_weekend_sp 	decimal(19,2) 			
season_id 	bigint(20)

[patjones]

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

[patjones]

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:

[B]tblRateType[/B]

fldRateID (tinyint) 	fldRateType (varchar(75))

1			           Weekend
2			           Weekday
3			           Weekend Special
4			           Weekday Special

[B]tblUserType[/B]

fldUserTypeID (tinyint)	fldUserType (varchar(10))
	
1		                  Member
2		                  Guest

[B]tblSeason[/B]

fldSeasonID (tinyint)     fldSeason (varchar(10))
	
 1		                Winter
 2		                Summer

Then you have a main rate table:

[B]tblRates[/B]

fldSeasonID  fldPatronType  fldDayType  fldRate

1		    1		      1		    40
1		    1		      2		    30
1  		  2		      3		    75
...		  ...		    ...	      ...