kinseymark Posted November 4, 2009 Share Posted November 4, 2009 Hi guys in real need of help with a database I want to put together for a real estate. So that people can search the site quicker and easier. I want it to show the area of the house, amount of rooms, minimal rental price and the maximum rental price. How do I go about this. Say I type in an area in myphp do I then have to add it multiple times for the different amount of rooms and rental price. Attached is an image to show what I have got so far, but now im stumped. PLEASE HELP ME. :'( [attachment deleted by admin] Quote Link to comment https://forums.phpfreaks.com/topic/180331-desperate-need-of-help-for-real-estate-database/ Share on other sites More sharing options...
Mchl Posted November 4, 2009 Share Posted November 4, 2009 Do you know what database normalisation is? If not, start with that (here are some good links: http://www.phpfreaks.com/forums/index.php/topic,126097.0.html ) Second thing is, you use wrong datatypes. CHAR() should be used for textual data. aream number of rooms, price - these are numeric data, and should be stored using numeric datatypes such as INTEGER or DECIMAL http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html Quote Link to comment https://forums.phpfreaks.com/topic/180331-desperate-need-of-help-for-real-estate-database/#findComment-951282 Share on other sites More sharing options...
kinseymark Posted November 5, 2009 Author Share Posted November 5, 2009 Still not sure my brain just cant figure this out and im completely confused, I have changed my datatypes but not sure what to do next? Do i add my areas ie, Birmingham, Edgbaston, Erdington but what do I do about the price and amount of rooms? Quote Link to comment https://forums.phpfreaks.com/topic/180331-desperate-need-of-help-for-real-estate-database/#findComment-951677 Share on other sites More sharing options...
kickstart Posted November 5, 2009 Share Posted November 5, 2009 Hi Normally you would have a table of locations, and a table of properties. Each property has a location field that contains the key of the location from the locations table. You would do something similar for property type Any line on the property table would contain the details for that property. You might have another table for (say) room sizes. So if a property had one room there would be 1 row on this extra table showing that rooms dimensions, similarly if a property had 100 rooms there would be 100 rows on this extra table showing those rooms dimensions. Once you have that kind of structure you can start to work out how to display it. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/180331-desperate-need-of-help-for-real-estate-database/#findComment-951709 Share on other sites More sharing options...
kinseymark Posted November 5, 2009 Author Share Posted November 5, 2009 Am i being really stupid? Now that I have my table is it just a case of adding all the properties into the table? Quote Link to comment https://forums.phpfreaks.com/topic/180331-desperate-need-of-help-for-real-estate-database/#findComment-951768 Share on other sites More sharing options...
kickstart Posted November 5, 2009 Share Posted November 5, 2009 Hi Basically yes, but split off some things to other tables. Such as area. That way you can provide a selection list for the area (from the other table) rather than relying on people to manage to get the spelling correct each time, and in your property table just refer to the index of the area from the area table. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/180331-desperate-need-of-help-for-real-estate-database/#findComment-951776 Share on other sites More sharing options...
kinseymark Posted November 5, 2009 Author Share Posted November 5, 2009 Sorry for being so dumb this is all new to me cant afford to go on a training course till next year so having to figure it out. So if I set up to tables; Table 1 is for area Table 2 is for rooms and price Quote Link to comment https://forums.phpfreaks.com/topic/180331-desperate-need-of-help-for-real-estate-database/#findComment-951787 Share on other sites More sharing options...
Mchl Posted November 5, 2009 Share Posted November 5, 2009 Seriously, read this: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html It is a very nice article. Quote Link to comment https://forums.phpfreaks.com/topic/180331-desperate-need-of-help-for-real-estate-database/#findComment-951790 Share on other sites More sharing options...
kinseymark Posted November 5, 2009 Author Share Posted November 5, 2009 Im now starting from scratch, when inputting the fiels for the table what does, PRIMARY, INDEX and UNIQUE mean? Quote Link to comment https://forums.phpfreaks.com/topic/180331-desperate-need-of-help-for-real-estate-database/#findComment-951792 Share on other sites More sharing options...
kickstart Posted November 5, 2009 Share Posted November 5, 2009 H Agree with Mchl. You appear to be trying to run before you can walk. That article (and others) will give you a basic idea of database design. With a relational database one main idea is to avoid duplicating details. Eg, if you were storing an electricity bill you wouldn't store the customers address with that bill (duplicating every other bill for that customer for decades), but you would store some kind of reference to the customers address. And each customers address might well not store the name of the county but instead a reference to a table of counties. First thing is to come up with a list of ALL the things you want to store. Some of those things will be common to some properties. From that you decide on a suitable structure of tables and how they join together. Once you have that structure you can start to work on how to populate it and display the details. In simple terms, primary means a primary key. Something that is unique to that row. Might be something meaningful (like a social security number) or something meaningless (such as a generated numeric key). Index is something that will be commonly used to narrow down searches for data (eg, property type, where you might have 1000 properties of which 50 are apartments). Unique just means something that is unique. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/180331-desperate-need-of-help-for-real-estate-database/#findComment-951801 Share on other sites More sharing options...
kinseymark Posted November 5, 2009 Author Share Posted November 5, 2009 Cheers guys sorry again for being a complete mysql idiot ill read the articel when Im home after work. Quote Link to comment https://forums.phpfreaks.com/topic/180331-desperate-need-of-help-for-real-estate-database/#findComment-951807 Share on other sites More sharing options...
kinseymark Posted November 5, 2009 Author Share Posted November 5, 2009 Hi again guys read through the documents just want to see if my thoughts are right. Table 1 = area table tabel _id and area 1 = bearwood 2 = erdington etc Table 2 = rooms table room_id and rooms 1 = 1 room 2 = 2 rooms etc Table 3 = price table price_id, minimum rent and maximum rent 1 = £100 - £250 2 = £251 - £300 etc now will table 4 be a mixture of the three tables above If im right which Im probably not then that document was brilliant the only part im struggling on is the joining, which has frazzled my brain! Help and thoughts would be appreciated. Quote Link to comment https://forums.phpfreaks.com/topic/180331-desperate-need-of-help-for-real-estate-database/#findComment-952058 Share on other sites More sharing options...
kickstart Posted November 5, 2009 Share Posted November 5, 2009 Hi Not sure I would use the rooms table like that. Maybe a table of rooms like: Id PropertyId RoomDescription RoomWidth RoomLength Then each property could have several rows on here, one for each room (depends how much detail you need). Room description could be an id pointing to another table (ie, 1 = master bedroom, etc). Similarly with price. I would keep that on the property table, unless you want to list it in some fixed price bands. However you have got the rigth idea. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/180331-desperate-need-of-help-for-real-estate-database/#findComment-952068 Share on other sites More sharing options...
kinseymark Posted November 5, 2009 Author Share Posted November 5, 2009 The property id is that a specific id code that I give to that house? For the rooms table it would be to complicated for tennants to search width and length would it be best to lay it out as Id PropertyId Rooms So the id would be 1, 2, 3 etc Property id would be a code maybe the estate agent uses for that property Rooms is for the amount of rooms for that specific property So each property has a specific id and the rooms. The price would be in fixed bands Are there any other documents that may be easier to understad regarding the joining of tables, as I feel thanks to you guys I have solved one issue but with another issue to follow? Quote Link to comment https://forums.phpfreaks.com/topic/180331-desperate-need-of-help-for-real-estate-database/#findComment-952089 Share on other sites More sharing options...
Mchl Posted November 5, 2009 Share Posted November 5, 2009 You should probably look at some example databases (available in the topic I posted my first link to) to see how they're designed. Quote Link to comment https://forums.phpfreaks.com/topic/180331-desperate-need-of-help-for-real-estate-database/#findComment-952096 Share on other sites More sharing options...
kickstart Posted November 6, 2009 Share Posted November 6, 2009 The property id is that a specific id code that I give to that house? Yes, probably just an autonumber key field. For the rooms table it would be to complicated for tennants to search width and length would it be best to lay it out as You can count the number of rooms, but have the details stored to display them. Are there any other documents that may be easier to understad regarding the joining of tables, as I feel thanks to you guys I have solved one issue but with another issue to follow? To join 2 tables, there are 2 basic kinds of join. Either an INNER JOIN, where you match the details from 2 tables and they are only returned if there is a match on both tables. Or an OUTER JOIN where if there is no matching record on one table the details from the other table are still returned. Most of the time you will be using an INNER JOIN. Say you had a table of properties:- Id HouseNumber Postcode 1 4 NG18 5SR 2 16 BT23 6QF And a table of rooms Id HouseId RoomName 1 1 Master Bedroom 2 1 2nd Bedroom 3 1 Box Room 4 2 Only room Then you could do something like:- SELECT * FROM Property JOIN Rooms ON Property.Id = Rooms.PropertyId That would give you one room for each room in each property. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/180331-desperate-need-of-help-for-real-estate-database/#findComment-952191 Share on other sites More sharing options...
kinseymark Posted November 6, 2009 Author Share Posted November 6, 2009 I understand the joining part to make them join do I just create another table with the id's I want to join thats the part thats confusing me? Quote Link to comment https://forums.phpfreaks.com/topic/180331-desperate-need-of-help-for-real-estate-database/#findComment-952543 Share on other sites More sharing options...
kickstart Posted November 6, 2009 Share Posted November 6, 2009 Hi How you do it depends on the situation. Basically is it a one to one, one to many, or many to many relationship. Eg, if you had a table of managers and a table of staff, then the staff rows would probably just have a column listing the managers ID, and you would join based on that, However If you had a table or properties and a table of facilities a property might have, where each property could have several facilities, and the same facilities are also in used in other properties then you would use an intermediate table to link them together (ie, it might have a structure of ID, PropertyId, FacilityId). All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/180331-desperate-need-of-help-for-real-estate-database/#findComment-952548 Share on other sites More sharing options...
kinseymark Posted November 6, 2009 Author Share Posted November 6, 2009 So mine would be a one to many as want it to read the areaId, roomsId and priceId. Quote Link to comment https://forums.phpfreaks.com/topic/180331-desperate-need-of-help-for-real-estate-database/#findComment-952554 Share on other sites More sharing options...
kickstart Posted November 6, 2009 Share Posted November 6, 2009 Hi Yep. There would be many properties with the same area (and so areaId) and many with the same price range (so the same priceId). All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/180331-desperate-need-of-help-for-real-estate-database/#findComment-952557 Share on other sites More sharing options...
kinseymark Posted November 6, 2009 Author Share Posted November 6, 2009 Thanks for your help, ill let know in the future how i get on with this database. Quote Link to comment https://forums.phpfreaks.com/topic/180331-desperate-need-of-help-for-real-estate-database/#findComment-952560 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.