Can ?I do this?

Databases
1

I’m creating a website and I think it might be cool if I do this…
You see the calendar on the availability page,
http://www.ontheocean.us/avail.shtml
What ?I want to do is make everything inside that fieldset dynamic.
I think I’m on the right track but want your guys input…
First ill create a table like this.
CREATE TABLE Availability (
id INT(5) NOT NULL PRIMARY KEY AUTO_INCREMENT,
display TINYINT(1),
year INT(4)
month INT(2),
open TINYINT(1),
cost VARCHAR(10)
)
then I can decide whether to display the calendar (0 or 1)
show the year (to go in the <legend>)
have the numerical value for month (1-12, Ill convert it to January… later)
determine if the condo is even available to be renter (0,1 to mean not open, open)
and finally determine the cost for that month.

This way I think I can make changes on the fly and will be a little more user friendly (if someone stupid want to enter in/update the Availability thing.

2

Actually, now that I think about it, is this the way to do it
Have two tables. 1 to hold the year and whether is to be displayed and another to hold each months data, So is this a good setup?
CREATE TABLE Availability_year (
year_id INT(2) NOT NULL AUTO_INCREMENT,
year int(4),
display TINYINT(1)
PRIMARY KEY(year_id),
FOREIGN KEY(year) REFERENCES Availability_month(year)
)
then have a linking table like this
CREATE TABLE Availability_month (
month_id INT(4) NOT NULL AUTO_INCREMENT,
year INT(4)
month INT(2),
open TINYINT(1),
cost VARCHAR(10)
)
Is this the best way to do this?

3

no, you’re going in the wrong direction

first of all, your table(s) for availability should focus on the thing that’s available, and not the years/months of availability (and you never mentioned what that thing might be – timesharing condo, i’m guessing? or something completely different)

your first decision is whether to keep track of the available dates (and the booking data would be kept elsewhere), or else keep track of the bookings (and the availability is then any time that is not booked)

what do you think is more important? my guess would be the bookings

4

Ya, your right, they are timeshare condos, but they get rented out on a month to month bases. Booking are the most important thing, are you saying to have a Bookings Table that would hold nothing but 2 dates (Start and End) and a cost (how would I determine that if I have a monthly price) but other than that I’d e lost on the makeup of the other tables. How many tales should Ihave?
Thanks…

5

i’m not sure how many tables you should have, all i know is that if you don’t keep track of your bookings, then displaying your availability is going to be pretty difficult

generating a calendar to show availability based on existing bookings is only slightly more complex than just keeping track of availability… but tracking availability without tracking bookings is difficult

even if there is only one resource, rather than the several resources shown in your sample link

6

You could start with something like this (pseudo-code):

condos

id
name
number
description

bookings

id
condos_id
month
year

condos table:

id
Auto-increment column

name
The name of the condo such as; El Camino. This column probably warrants a unique constraint. It looks like all the names are unique so you might as well require the name to the be unique. This could be the primary key if you like if all names are unique.

number
The condos number; such as 409. Depending the condo “number” format number might not be the best name or it may be best to make it a varchar. I have not idea though. It looks like they are all integer values but for future purposes its probably better to make the column a varchar and name it something better. Sure you get the idea though. This column would obviously warrant a unique constraint. Perhaps code is a better name for the column.

description
The description on of the condo. This would be the HTML for the description pages probably including the amenities and photos. I don’t think its necessary to start factoring the amenities and photos out unless you really want to.

bookings table:

id
The auto-increment primary key (you could probably scratch this column if you like using a composite key for the year, month and condo in combination). Many times its nice to have a surrogate key for the application end of things.

condos_id
Foreign key to the condo the booking is being made for.

month
Month could either be a foreign key to a month in a separate table containing all months or simply a a numerical value. Using a separate table would probably yield more easy reporting, etc considering there is something to join on. Either way month would conceptual represent the month the booking was made for.

year
The year the booking was made.

Now these rules provide enough information to determine whether a condo is available or not using the above schema.

  1. Summer Rental minimum is 3 months- either June/july/Aug or July/Aug/Sept
  2. Winter Rental minimum is 3 months - either Jan/Feb/March or Feb/March/April

For example, if someone booked in June than the condo will not be available until July. That can be derived within the query or application end of things and doesn’t really need to be stored in the database.

You could even add a single column to the condos table to account for the minimal number of months a rental may be booked. At this point in time they are all three but that could change so it would be a good gesture to account for that on the client-behalf of future expansion and business modification. You go farther and add separate columns for winter and summer so that the minimum number of months could be different for winter and summer months for any given condo.

That is a basic idea of how it could be approached.

7

Oh wow, I never thought of it like that…THANKS

8

ok is this good logic,

CREATE TABLE Buildings (
id INT(4) NOT NULL PRIMARY KEY,
name VARCHAR(15)
)
CREATE TABLE Condos (
id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
building_id INT(4) REFERENCES Buildings(id),
number INT(4),
description LONGTWEXT
)
CREATE TABLE Bookings (
id INT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
month int(2) REFERENCES Month(id),
year INT(4)
)
So, id have 3 tables. The only thing that i’m a little hazy on is uploading pictures (variable number) for each condo. I thought tghat this should be done by adding a fourth table…
CREATE TABLE Photos (
id INT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
condo_id INT(4) REFERENCES Condos(id),
image VARCHAR(30)
)
where image would be the relative path to the uploaded image.
I guess I can make 2 tables in the place of Photos, so I can etter organize the photos with the condos like this,
CREATE TABLE Units (
id INT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
gallery INT(4) REFERENCES Condos(id),
)
CREATE TABLE Image (
id INT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
unit INT(5) REFERENCES Units(id),
Image VARCHAR(30)
)
Then each condo would have a gallery of photos.
Which way do you think is best for doing this?
Thanks.

9

Since you are using more multiple pictures for each condo it’s a good idea to have a separate table for that!

10

Unless a condo can have multiple galleries / different categories of photos its most practical and simple to use a single table for the images with a foreign key to the condo. Anything else may be somewhat of an overkill for the requirements. Its not wrong, just makes things more complex than perhaps they need to be.

Also, it may do you some good to look up the actual meaning of INT(x) because I don’t think it is what is intended. For example, INT(4) doesn’t mean 4 numbers, it means something completely different. If you are going to make that an integer use TINYINT not INT(4). I’ll leave the rest for you to look-up but its important to know that INT(x) does not mean x numbers allowed.

To the image table you may want to add some meta data such as; image size, mime type, width and height. Those are always good have for later purposes. Perhaps a description and label also so that the location can be used internally while the label/title displayed to the user can be changed without breaking anything. The other one is a caption which is good to have.

11

this is what you mean?

12

yes, exactly

:slight_smile:

13

so now since INT is not needed, the 4 tables would look like;

CREATE TABLE Buildings (
id SMALLINT NOT NULL UNSIGNED PRIMARY KEY,
name VARCHAR(15)
)

CREATE TABLE Bookings (
id TINYINT NOT NULL UNSIGNED AUTO_INCREMENT PRIMARY KEY,
month TINYINT UNSIGNED REFERENCES Month(id),
year SMALLINT UNSIGNED
)

CREATE TABLE Condos (
id TINYINT NOT NULL UNSIGNED AUTO_INCREMENT PRIMARY KEY,
building_id TINYINT REFERENCES Buildings(id),
number SMALLINT UNSIGNED,
description LONGTEXT
)

CREATE TABLE Photos (
id SMALLINT NOT NULL UNSIGNED AUTO_INCREMENT PRIMARY KEY,
condo_id TINYINT REFERENCES Condos(id),
size SMALLINT NOT NULL UNSIGNED,
type VARCHAR(15),
width TINYINT NOT NULL UNSIGNED,
height TINYINT NOT NULL UNSIGNED,
label SMALLINT UNSIGNED
)
CREATE TABLE Buildings (
id SMALLINT NOT NULL UNSIGNED PRIMARY KEY,
name VARCHAR(15)
)

CREATE TABLE Bookings (
id TINYINT NOT NULL UNSIGNED AUTO_INCREMENT PRIMARY KEY,
month TINYINT UNSIGNED REFERENCES Month(id),
year SMALLINT UNSIGNED
)

CREATE TABLE Condos (
id TINYINT NOT NULL UNSIGNED AUTO_INCREMENT PRIMARY KEY,
building_id TINYINT REFERENCES Buildings(id),
number SMALLINT UNSIGNED,
description LONGTEXT
)

CREATE TABLE Photos (
id SMALLINT NOT NULL UNSIGNED AUTO_INCREMENT PRIMARY KEY,
condo_id TINYINT REFERENCES Condos(id),
size SMALLINT NOT NULL UNSIGNED,
type VARCHAR(15),
width TINYINT NOT NULL UNSIGNED,
height TINYINT NOT NULL UNSIGNED,
label SMALLINT UNSIGNED
)

I do have a few questions though. Why would I want to know the size (in bytes) of the image? Same for the mime type (wouldn’t that just be the extension)? Also why would I want to know the width and height (in pixels) of the image? As for the label, that can be the condo number, but it would be the same as the number column in the Condo table so I dont know if I need that?
Lemme know if I covered all the bases…
thx