question about what type of data to use

[DewKnight]

Hi... I have some questions about mysql, and what data types I should use. I am using php/mysql to make it easier to update a website. I am pretty new to using mysql, and don't know which data types I should use for certain fields (blobs, text, all that)... I was wondering if you all might be able to point me to a site that kind of explains that, or if you might suggest what I should use... The page that I am working on, you can see here: http://coloradomountaincabins.com/test/cabin-page.php

What I am going to be doing is storing all of the information for a cabin in a database. I will them have the ability to edit the cabin data by using another page (similar to this one: http://coloradomountaincabins.com/test/cabin-edit.php ). What types should I be using for the data? The images will not be stored in the database, because I want them to be able to change them easier...

Thanks for your help, please let me know if I didn't explain this well enough, sometimes I just go off about random stuff.

[sde]

hi Dew, welcome to the site.

some of this will be guess work since i don't know all the data you want to trac, but i'll use the specs on your page to form a sample of what the table might look like.

- cabin_id - integer, unsigned, auto-increment. this is going to be a field that keeps a unique number for every cabin. when you insert a new cabin, you don't have to set this field because mysql will automatically set it for you. using ids makes it much easier to work with data.

- cabin_name - varchar (64). 64 is just a guess, but basically that is how many characters you can have in this field.

now for "Location" down to "Special Features", you could probably use varchar with the same logic as cabin_name. varchar can go up to 255 characters, so if you need more than that, use text.

special_atmosphere - medium text.

if the format of your rates for each cabin don't change, meaning for each cabin there will always be a daily weekday peak, daily weekday off, holiday weekly peak, holiday weekly off, 7-day .. etc, .. then you can probably use the same table for your rates to keep things simple.

rate_daily_peak - integer(4) unsigned - unsigned just means the number will never be negative. since you're fees are not decimals, you can just use integers. if you charged cents, then you would have to use decimal(4,2)

so basically just go on with the rate_daily_peak for all the rest of your charging fields

rate_daily_off
rate_holiday_peak
rate_holiday_off
rate_7day_peak
rate_7day_off

if the information describing the peak season, off season, and the text under the rates, are the same for every cabin, .. you could probably create another table or just hard code it into the page.

technically you could store images inside the database with a blob, but i would recommend just storing them in a directory on the server. working with images in the database can get slow and difficult to manage.

use a naming scheme like: images/cabins/<cabin_id>/. then you can use PHP to read the directory for the cabin id to create an array of photos.

that place sure looks nice

[DewKnight]

Awesome, that helps a lot.

So pretty much I will be having a table, with these fields in it, and all new cabins will be creating a new row in this table, correct?

There are a set number of cabins, most of the time, no new cabins are added, just cabins are removed from the list and new cabins are put into their place. Probably what I will do is set up the default data for cabin numbers 1-40, then for the cabins that are not currently being use, I will just set it to not display those. All of the cabins will be found on the Our Cabins page, here: http://www.coloradomountaincabins.com/our_cabins.html (as you can see, there are quite a few).

So thank you very much for your help in describing this to me.

By the way, found the site from an ad on Fark.com

[redhead]

One thing I've noticed when trying out these types, is, when using type TEXT fields as opposed to BLOB, you can make case sensitive searches, BLOB dosn't support that.
From your layout, I recon a table design similar to this might work:

CREATE TABLE location (
    id INT(11) DEFAULT '0' NOT NULL auto_increment,
    name VARCHAR (20),
    street VARCHAR (25),
    number INT(6),
    town VARCHAR (25),
    zipcode INT(6),
    atmosphere BLOB,
    UNIQE id,
    PRIMARY KEY id
    );
CREATE TABLE cabin (
    id INT(11) DEFAULT '0' NOT NULL auto_increment,
    name VARCHAR (20) NOT NULL,
    size INT(8),               # squarefeet of cabin
    location_id INT(11),
    bath INT(1) DEFAULT '0' NOT NULL, # does it have a bath (1: yes, 0: no)
    water INT(1) DEFAULT '0' NOT NULL, # does it have running water (1: yes, 0: no)
    heat INT(1) DEFAULT '0' NOT NULL, # does it have heat (1: yes, 0: no)
    electric INT(1) DEFAULT '0' NOT NULL, # does it have electricity (1: yes, 0: no)
    other BLOB,
    UNIQE id,
    PRIMARY KEY id,
    FOREIGN KEY (location_id) REFERENCES location
    );
CREATE TABLE cabin_pics (
    id INT (11) DEFAULT '0' NOT NULL auto_increment,
    cabin_id INT(11) NOT NULL,
    name VARCHAR(11) NOT NULL,
    UNIQE id,
    PRIMARY KEY id,
    FOREIGN KEY (cabin_id) REFERENCES cabin
    ); 
CREATE TABLE beds (
    id INT (11) DEFAULT '0' NOT NULL auto_increment,
    cabin_id INT(11) NOT NULL,
    UNIQE id,
    PRIMARY KEY id,
    FOREIGN KEY (cabin_id) REFERENCES cabin
    ); 
CREATE TABLE feature (
    id INT (11) DEFAULT '0' NOT NULL auto_increment,
    cabin_id INT(11) NOT NULL,
    description BLOB,
    UNIQE id,
    PRIMARY KEY id,
    FOREIGN KEY (cabin_id) REFERENCES cabin
    );

Now your Electric, bathroom, heat, water choices I guess are just true/false values, since they seem to just be some info on wether the cabin has got it or not.. So these can be simple check boxes, instead of text boxes..
So you store a '1' when they have it, and a '0' when they dont.

This layout provide you with some scalability, you can have any number of pictures assigned to a cabin, just queue something like "SELECT * FROM cabin_pics WHERE cabin_id = <some id>"
And you can all sorts of beds assigned to a cabin this way, aswell as any number of cabins located at the same location.. etc.

But then again, this is just a suggestion... I'm sure alot of the users here can come up with alot of other solutions.

[sde]

Originally Posted by DewKnight

There are a set number of cabins, most of the time, no new cabins are added, just cabins are removed from the list and new cabins are put into their place. Probably what I will do is set up the default data for cabin numbers 1-40, then for the cabins that are not currently being use, I will just set it to not display those. All of the cabins will be found on the Our Cabins page, here: http://www.coloradomountaincabins.com/our_cabins.html (as you can see, there are quite a few).

So thank you very much for your help in describing this to me.

By the way, found the site from an ad on Fark.com

awsome, we're getting a good response from the fark ads.

even though you will only have 40 cabins, i still recommend using the cabin_id. this isn't something you need to display, but it makes things easier to manage when each cabin has a unique id.

if you want to assign them a 1-40 number, you could also do that, just make a new field for cabin_number or something.

like red says, there's a lot of ways to do it.. but between our 2 posts, you shoud have a good place to start.

keep us updated.

[DewKnight]

Yes I will still use the cabin ID, but what I will do is first just create all of the cabins. Then I will have all of the cabin id's set out that I need, and then I can create more cabins later if I need to... I will display the cabin ID (use it as the cabin number).

The electric pretty much will always be available, but the heat and water will vary, sometimes a cabin may be forced air, or radiant, or fireplace. The water can be well water, cistern, city, etc. The bathroom is how many bathrooms a cabin has, and if they are full, half, etc.

Thank you very much for the replies, and the sample code. So you are saying that I should probably put the parts with much more text in different tables? Rather than have all information about a cabin in one table?

There is also the Our Cabins page that will be displaying this info, but I'll just be grabbing it from the info for a cabin page.

With the explanations and the sample code, I should be able to figure out what I want to do easily. Thanks again.

[DewKnight]

Ah, I see what you are saying about the beds now I believe, as well as the pictures, set them in different tables, and just assign them to the cabin number they belong to.

[redhead]

Ah, I see what you are saying about the beds now

Yes, you get the idear, if you have 4 different types of beds, you only need that info four times in yoru database, you can just fetch it from the cabin info by refferencing the beds_id you've got stored there..
The same thing with location, so you don't have to maintain dupes of some redundant info.