Forum Moderators: phranque
its based on renting houses, and the users will be able to search on Location, Area, House name, Amount of rooms, price range, garage.... etc...
I can create the SQL for these OK, but im designing an access database and am not sure whether i should use just one table with all the info, or a number of tables, eg a table for Loactaion and area, etc.
Could someone please advice the best way and why?
thankyou
added:
Oh, why?
Save space and avoid anomalies.
If you set up one main table with everything in, then set up lots of queries from this which are relevant to your particular search criteria..the connect the page to the query instead of the main table
Suzy
I cant really see how i can normalise this, or am i missing something?
If you set up one main table with everything in, then set up lots of queries from this which are relevant to your particular search criteria..the connect the page to the query instead of the main table
Actually, it's quite opposite.
You normalize data by splitting it into different tables with different relations and then use views (you can use queries in access as views) to join data in any way you need.
With the sort of data you're going to be handling, I'd suggest you use lookup tables for a variety of items e.g. "Location".
Why?
'cos if you do it that way you always have very clean data and you don't end up with a DB full of stuff that doesn't quite match what it should be.
Example:
(1) data comes from lookup table which contains the entry "North and Central Somewhere"
-> all entries are exactly that.
(2) data gets typed in willy-nilly
-> you get entries like
"North and Central Somewhere"
"North & Central Somewhere"
"NC Somewhere"
makes your life a lot easier if you're trying to search (1)
Maybe i could have a table with location, table with area and then all the houses in a seperate table
Andy
If you set up one main table with everything in, then set up lots of queries from this which are relevant to your particular search criteria..the connect the page to the query instead of the main table
Actually, it's quite opposite.
You normalize data by splitting it into different tables with different relations and then use views (you can use queries in access as views) to join data in any way you need.
Yes you're quite right...I just didn't explain very well at all :(
That's what I meant (honest guv!, it's what I do anyway:)) by setting up a main table..but of course it should've been main tables with relationships.
hadn't meant to confuse the issue it's just that some people don't know that you can use queries (views) in this way and that had been the point I was tring to make
Sorry
Suzy
<added> Andrew yes, then in your houses table have "lookup" columns to link to the other tables</added>
I did an online estate agents and within days they wanted to add more uk regions, then more house types.
Do it properly and they can administer it all themselfs without worry of breaking it.
Its just one single table for properties and one table for each dimension.
If it is a small setup, I'd do whatever is easiest to program and then let the program do any post processing. If it is large or a huge db where speed is going to be a consideration, then spreading out for easy xrefing is the way to go.
Im doing it in Access first, and then when i get to grips with SQL database, and if it needs it, i will migrate over.
As far as the design is concerned, i want to do it the right way first time, so i sounds as if normalisation is the way to go.
thanks for all the posts
Example:
You're building a search form and in your database there are 5 regions that correspond to your data. When you build your search form, you could hand code a drop down menu box to have these 5 regions displayed and it would work fine. However, if you ever added, deleted, or changed the name of this region, you would have to edit html that generates the menu box.
If your data is normalized and the regions are in a seperate table, you can dynamically create a menu box with the values from that table. So if there were ever an edit made to that data, it would instantly be reflected in your search form. Yeah there is more overhead here, but it is worth checking out.
I would also caution against MS Access. I have had lots of data corruption problems when multiple connections are made to the database. It may possibly be worth your while to delay production (if its an option and I know that it probably isn't) and learn SQL Server so that you don't grow into this problem. It's not nearly as difficult as some make it out to be, assuming that somebody else (a webhost?) would be doing the set up and maintenance.
With that goes the possibilty of being plundered and pillaged by M$. Think about using mySQL. Lots of support and it works very well. A bit of a learning curve now and you wont have to bother with a complex changeover later on.
Cheers
I have a few domains with this reseller, and they are good for the money. Im worried if i change i will be worse off, etc etc.. I basically want to build everything up, and then see if it is worth while changing databases, hosts etc... I just want to get the project off the ground
If the answer is call it from Access, how do i do this?
thanks
Let Access do the work as opposed to ASP.
Have you got a copy of Dreamweaver UltraDev or MX. That lets you do the sorts of things you are after very easily (little messy with the code though)
Absolutely! Always let the database program, whatever it is, do the work of querying and manipulating the data, and use ASP simply to retrieve and send data. That will speed thigs up quite a bit, but it does mean designing the database with more care.
Onya
Woz
Yes, ive got MX, but im not sure how to link to the query, is it the same as setting up a recordset?
Yes exactly the same
"SELECT * FROM queryTable"
(PS I always give my query tables a name that starts "que" e.g queTableName..just so I know where I'm calling from)
Suzy
[edit]sp![/edit]
My SQL Server suggestion is based off of the fact that SQL server offers features like Views and Stored Procedures that integrate easily with ASP. Using MySQL (which apparently isn't even supported) with ASP requires a MySQL driver that most web hosts that I've seen do not install.
My only problem with MySQL is that it doesn't have either of the above, nor does it have triggers. While these are not entry level features, the sooner that a developer learns how to use them the more effective (and portable) the code becomes. Access and mySQL can't offer this level of power, though MySQL is certainly very fast, which has to count for something.
As for the plundering, you should know that a competent administrator can prevent this.
As for the plundering, you should know that a competent administrator can prevent this.
Recently I got hammered by M$ during an upgrade. What I found out was that on Win 95, Exchange was free, now it is not and I was required to find around £800 so that three PC’s could share each others diaries and contacts. To buy a copy of Office for each machine also costs a small fortune (£3-400) because they don’t do an upgrade for 95. Now if I was a student I could buy XP Office Pro for £100 but I cant. No wonder there is piracy and a general dislike to M$.
When all is said and done I like M$Office I just don’t like the prices.
Off topic I know...
My only problem with MySQL is that it doesn't have either of the above...
I could enver understand why people use MySQL when PostgreSQL exists :)
