| Author |
Topic |
|
Shep
Starting Member
19 Posts |
 Posted - 2013-07-03 : 10:34:02
|
| I am relatively new to programming but do understand the concepts.A friend of mine owns a house rental company and asked me to build a site that has many functions. Those functions will be:(Phase One) Have all the houses in the database but only show the available houses on the site. (Phase Two) Online application. When in a detailed view of the house, click a "Apply Now" button and that house info in imported into the application.(Phase Three or even later) Renters can log in and pay rent, report a service call, or other renters needsThats the most of what it needs to do.I need suggestions on how to create the database. For example, what items should pull out of the main table.I could probably build the house stuff in one table but I dont think it is recommended. Any advice is greatly appreciated.Thanks in advanced,JSJ.E.Shepler |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-03 : 10:48:44
|
| My initial thoughts (just about the houses, not the rest of the stuff) is to start with 2 or 3 tables like this:1. Houses. This would have columns like, UnitId (primary key, perhaps an internally generated number), Address, Building Number, NumberOfRooms etc. In other words all the data relating to that entity, regardless of whether it is rented out or not. An additional column that indicates whether the unit is currently available or not.2. Rentals. This would have information about the rental status of each unit. UnitId (from Houses table), RenterID, LeaseStartDate, LeaseEndDate, etc. 3. Renters. This would have information about the people who (may or may not) have rented. Firstname, lastname, cellphone number etc.What I am thinking is that there will be multiple rows in the Rentals table for each unit as someone leaves and a new person rents it.There are some aspects of this design that I don't like/am not completely satisfied with. For example, if I had to do it, I would split the renters table into a Rentals and RentalsHistory table, so there will be only at most one row in the Rentals table for a given unitid. I am sure others will contribute and you can improve on it based on your needs. |
 |
|
|
Shep
Starting Member
19 Posts |
Posted - 2013-07-03 : 11:02:12
|
| For the houses, I was somewhat on the same page. I would think having a table for "available" and a column in houses that linked to it. Giving it a value of Y/N.What another tables should I, or could I, create to break down the repetitious data.The columns I would think for the house would be:HouseIDAddressCityStateZipBRBAAvailablePhoto (Not sure if this should go in database or images folder)DescriptionFeatures (such as Central heat/air, fenced yard, gated area, etc)PriceSchool District (would be a nice feature)So, of those items, what would have its own table connected via foreign key?Thanks againJ.E.Shepler |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-03 : 11:31:08
|
| If you have multi-unit rentals, yes, I would have one table for Building and another for RentalUnit with a one to many relationship. BuildingId would be a foreign key in the RentalUnit table. City, State etc. would be in the Buildings table. Fenced yard should be in the buildings table; air-conditioning might be in the RentalUnits table (if for example some units have central air conditioning, some have none, and others have window airconditioning etc.) |
|
|
|
Shep
Starting Member
19 Posts |
Posted - 2013-07-03 : 13:38:42
|
| I responded a few hours ago but it doesnt seem to be showing.He has about 40 houses all together.I'm not really sure what your referring to as the Buildings and RentalUnits tables. They would be the same I would think.J.E.Shepler |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-03 : 13:49:55
|
| What I had in mind when I referred to Buildings and RentalUnits was a multi-unit building (aka apartment building) where there are several units within one physical structure, each of which may be rented out to a different individual with different terms and conditions. |
|
|
|
Shep
Starting Member
19 Posts |
Posted - 2013-07-03 : 13:51:14
|
| That is not the case with his properties. They are all houses with sepperate addresses.J.E.Shepler |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-03 : 16:05:48
|
| Even so, it may not be a bad idea to separate out the property from the rental unit - in case he decided to buy multi-unit dwellings, or perhaps decides to convert a building into his own residence, or storage unit, or whatever else.Even if none of those things happen, conceptually in my mind, there are two entities - one a building, and the other a rental unit. In your case there just happens to be a one-one relationship.But, it is a judgement call; do whichever you feel comfortable with. Sometimes one can be too normalized and cautios and end up with unnecessary tables. |
|
|
|
Shep
Starting Member
19 Posts |
Posted - 2013-07-04 : 12:13:09
|
| Thanks for the pointers James.My next question has to do with photos.Every house will have at least 1 main photo and could possibly have more available once the particular house is clicked on.So where are the photos stored and referenced. And how do you do it.I havent worked with this AT ALL and dont even know where to begin.Thanks again for all your pointersJ.E.Shepler |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-04 : 12:33:42
|
| I would keep the photos in a separate table. The table would have at least 3 columns - a reference to the rental unit and/or the building, a photo id, and the actual image itself. The reason you want a separate table is at least two: a) there can be a one to many relationship between a unit and photos, and b) images are stored "off-row" in a user table, which has storage and performance implications, so storing that separately is better to free the main rental unit table from having to handle images as well.I have not experimented with this much, but you can store the images as binary files in SQL Server tables or you can store them in the file system using filestream. SQL 2008 and later has filestream facility. In SQL 2012 it has been enhanced, if what I recall correctly http://msdn.microsoft.com/en-us/library/gg471497.aspx |
|
|
|
Shep
Starting Member
19 Posts |
Posted - 2013-07-05 : 15:24:17
|
| I'm using Sql2008R2 If I put the photos in a table and each house has a different numbers of photos, would you create a row for each image and then connect the photos back to the house by using the HouseID? Or would you list the House and then have a column for "X" number of pictures?The main page would only have one "Cover" image, most likely the outside picture.Then, when you click on the property it would take you to a more DETAILED page that would include a photo gallery of the house. It would offer a more detailed description of the home, along with the added info not available on the main properties page such as amenities, school district, square feet, etc.What do you think?Thanks againJS |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-05 : 16:01:24
|
| Definitely create a separate table for photos. Four columns: HouseId, PictureId (just an integer id), IsDefaultPicture (to tell you whether this is the picture that you want to show on the main page), and then the image column.When you want to display the main picture, it would be a simple join with the Pictures table on "HouseTable.HouseId = PictureTable.PictureId AND IsDefault=1". When you go to a detail page, you will have the houseId available, and you would select Pictures from the PictureTable where HouseId = theHouseIdOftheHouse. |
|
|
|
Shep
Starting Member
19 Posts |
Posted - 2013-07-05 : 18:56:48
|
| So in the main table, "Properties", the columns should be:HouseIDAddressCityStateZipBedroomsBathroomsPricePhotoID (How would you do this one)DescriptionAvailableTenantIDIs that it for the main table? And do I have them named properly or as expected?J.E.Shepler |
|
|
|
|
Database Design suggestions