SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Database Design suggestions
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Shep
Starting Member

USA
19 Posts

Posted - 07/03/2013 :  10:34:02  Show Profile  Reply with Quote
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 needs

Thats 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,
JS

J.E.Shepler

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/03/2013 :  10:48:44  Show Profile  Reply with Quote
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.
Go to Top of Page

Shep
Starting Member

USA
19 Posts

Posted - 07/03/2013 :  11:02:12  Show Profile  Reply with Quote
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:
HouseID
Address
City
State
Zip
BR
BA
Available
Photo (Not sure if this should go in database or images folder)
Description
Features (such as Central heat/air, fenced yard, gated area, etc)
Price
School District (would be a nice feature)

So, of those items, what would have its own table connected via foreign key?
Thanks again


J.E.Shepler
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/03/2013 :  11:31:08  Show Profile  Reply with Quote
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.)
Go to Top of Page

Shep
Starting Member

USA
19 Posts

Posted - 07/03/2013 :  13:38:42  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/03/2013 :  13:49:55  Show Profile  Reply with Quote
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.
Go to Top of Page

Shep
Starting Member

USA
19 Posts

Posted - 07/03/2013 :  13:51:14  Show Profile  Reply with Quote
That is not the case with his properties. They are all houses with sepperate addresses.

J.E.Shepler
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/03/2013 :  16:05:48  Show Profile  Reply with Quote
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.
Go to Top of Page

Shep
Starting Member

USA
19 Posts

Posted - 07/04/2013 :  12:13:09  Show Profile  Reply with Quote
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 pointers


J.E.Shepler
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/04/2013 :  12:33:42  Show Profile  Reply with Quote
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
Go to Top of Page

Shep
Starting Member

USA
19 Posts

Posted - 07/05/2013 :  15:24:17  Show Profile  Reply with Quote
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 again
JS


Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/05/2013 :  16:01:24  Show Profile  Reply with Quote
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.
Go to Top of Page

Shep
Starting Member

USA
19 Posts

Posted - 07/05/2013 :  18:56:48  Show Profile  Reply with Quote
So in the main table, "Properties", the columns should be:

HouseID
Address
City
State
Zip
Bedrooms
Bathrooms
Price
PhotoID (How would you do this one)
Description
Available
TenantID

Is that it for the main table? And do I have them named properly or as expected?

J.E.Shepler
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000