SitePoint Sponsor

User Tag List

Results 1 to 13 of 13

Thread: Can ?I do this?

  1. #1
    SitePoint Wizard lukeurtnowski's Avatar
    Join Date
    Mar 2003
    Location
    Coronado
    Posts
    1,666
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Can ?I do this?

    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.
    "Oh, and Jenkins--apparently your mother died this morning."

  2. #2
    SitePoint Wizard lukeurtnowski's Avatar
    Join Date
    Mar 2003
    Location
    Coronado
    Posts
    1,666
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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?
    "Oh, and Jenkins--apparently your mother died this morning."

  3. #3
    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)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard lukeurtnowski's Avatar
    Join Date
    Mar 2003
    Location
    Coronado
    Posts
    1,666
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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...
    "Oh, and Jenkins--apparently your mother died this morning."

  5. #5
    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)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    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.
    The only code I hate more than my own is everyone else's.

  7. #7
    SitePoint Wizard lukeurtnowski's Avatar
    Join Date
    Mar 2003
    Location
    Coronado
    Posts
    1,666
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Oh wow, I never thought of it like that...THANKS
    "Oh, and Jenkins--apparently your mother died this morning."

  8. #8
    SitePoint Wizard lukeurtnowski's Avatar
    Join Date
    Mar 2003
    Location
    Coronado
    Posts
    1,666
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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.
    "Oh, and Jenkins--apparently your mother died this morning."

  9. #9
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,164
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Since you are using more multiple pictures for each condo it's a good idea to have a separate table for that!
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  10. #10
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    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.
    The only code I hate more than my own is everyone else's.

  11. #11
    SitePoint Wizard lukeurtnowski's Avatar
    Join Date
    Mar 2003
    Location
    Coronado
    Posts
    1,666
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    "Oh, and Jenkins--apparently your mother died this morning."

  12. #12
    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)
    Quote Originally Posted by lukeurtnowski View Post
    yes, exactly

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Wizard lukeurtnowski's Avatar
    Join Date
    Mar 2003
    Location
    Coronado
    Posts
    1,666
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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
    "Oh, and Jenkins--apparently your mother died this morning."


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
  •