Topic awaiting preservation: Database Type

Hi all, just a few database questions. Personally i have alot of experience with MS Access databases and creating systems (that have nothing to do with the internet)

I want to set up a site with say up to 500 products, stored in the database. With a basic setup like brand, description, name, price.

So what i am curious about:
Q1. For a database this size is there really much difference between a MS Access and SQL database for a website in terms of speed?

Q2. When you access a database from a site, does the physical size of the file matter? Or is the speed only effected by the tables/data you are accesssing. I.E is it better to have more databases that are smaller rather than one database with all the data. Or is it irrelevant.

Q3. One of the things i am considering is having an offline product management system, so there can be like a front end with which they update pricing, and then upload the new database file via a site page. This way i can make the interface exactly how i want it without the restrictions that you might have doing it through a website. Now i know how to do this with Access just fine, but SQL i am not sure about, can you have similar front ends like you can with access, and is it alot more work to do etc?

Q4. Yay last question - If i did go with MS Access database now, and eventually decided i wanted to change to SQL, is there an access to mySQL database converted, and does it work properly. Is it hard to do?..


Anyhow, i'm reading what i can on the net, but alot of this comes down to some experience that i just dont have ;(. So thanks everyone in advance for any information!

quote:

---

Q1. For a database this size is there really much difference between a MS Access and SQL database for a website in terms of speed?

---

At that size, with mostly a read only database - completly irrelevant. Both systems will have the database in memory, sooner or later (sql via the db's query cache, probably, and access at least via window's file system cache)

quote:

---

Q2. When you access a database from a site, does the physical size of the file matter? Or is the speed only effected by the tables/data you are accesssing. I.E is it better to have more databases that are smaller rather than one database with all the data. Or is it irrelevant.

---

Depends on what you're doing... Mysql for example stores every table(!) in a seperate file anyhow. Speed is extremly effected by the queries you run, and what indizes exist over the data tuples.
Databases are logical groups of structuring, they won't affect speed (much), but they carry an overhead in storage requirements (think database headers). Same goes for tables. Your data should be in c2->NormalForm anyhow.

quote:

---

Q3. One of the things i am considering is having an offline product management system, so there can be like a front end with which they update pricing, and then upload the new database file via a site page. This way i can make the interface exactly how i want it without the restrictions that you might have doing it through a website. Now i know how to do this with Access just fine, but SQL i am not sure about, can you have similar front ends like you can with access, and is it alot more work to do etc?

---

Well, you'll need some kind of client sql engine for manipulating the data locally, and uploading larger dumps via php is an annoyance at best. But then, you can change single data sets without reuploading the complete, possibly big, database with sql.

quote:

---

Q4. Yay last question - If i did go with MS Access database now, and eventually decided i wanted to change to SQL, is there an access to mySQL database converted, and does it work properly. Is it hard to do?..

---

Depends on wether you stuck to standard sql - if you did, and you sticked to a standard old enough (there are serveral revisions of sql) the transition should be from 'you don't need to do anything to the actual code if you wrapped the database beforehand' to rewriting serveral queries. If you didn't - tough luck, you'll need to rewrite quite a bit by hand.

quote:

---

Anyhow, i'm reading what i can on the net, but alot of this comes down to some experience that i just dont have ;(. So thanks everyone in advance for any information!

---

Indeed - what you don't seem to have is a general knowledge of relational algebra and its applications to database. Start with that c2->NormalForm and make sure to read c2->RelationalDatabase as well.

so long,

->Tyberius Prime

Thats great Tyberius, thanks alot for the info! As always this opens me up for more questions! If you can provide further details that would be great, also im reading through those links you gave me thanks for those too!

quote:

---

Mysql for example stores every table(!) in a seperate file anyhow.

---

I was wondering how sql stored data!

quote:

---

Speed is extremly effected by the queries you run, and what indizes exist over the data tuples

---

Are there certain rules with queries that have major effects on speed, do this but never do that? Doing queries isnt particularly hard and i guess if your database is structured properly then that helps too. Im guessing that speed issues start getting noticable when doing unions etc, and crossreferencing data - but if its a straight pull data from a table where the brand = 'something' then is there really anything you can/need to do to speed it up?

quote:

---

Well, you'll need some kind of client sql engine for manipulating the data locally

---

Are there standard things for this? Or is it something i have to write. Obviously for an Access Database i just use the MS Access software to create the forms (or alternatively through VB).. Maybe its possible to use a MS Access front end with an SQL database? not sure if that is possible..

quote:

---

and uploading larger dumps via php is an annoyance at best

---

Hmm, is this a php issue or a server timeout issue? If my access database got to 15mb or something would i have trouble uploading it? The database isnt likely to get anywhere near this big for only 500 items i guess without a front end..

quote:

---

If i did go with MS Access database now, and eventually decided i wanted to change to SQL, is there an access to mySQL database converted, and does it work properly. Is it hard to do?..
Depends on wether you stuck to standard sql

---

Hmm, are you refering to the code i assume, how about as far as the data in the tables. I spose that is straight forward saving your table data as SQL files. Im assuming worst case i would have to rewrite the queries and some code with sql syntax - but not recreate the tables themselves?


Hmm this could possibly make a great FAQ on this topic?

Meanwhile i will read those links you gave me and see what i can suss out, thanks again!

All right. More info.

quote:

---

Speed is extremly effected by the queries you run,[...]

---

Simple rules (for mysql at least): Conditions (and sorting) on fields without an index are slow (the whole table has to be read). If in doubt do a 'explain select [your query here]' - and read the manual chapter that explains what it outputs. Extremly helpfull when you got a slow query - only start worrying about all this when your app runs to slow - profile first - you don't want to optimize a gazillion little queries when you can get the same improvment by changing just a couple.
Ps: like '%something%' in mysql doesn't use *any* indizes. Guess why it's so slow to search for a user's posts in the grail. (hint 300mb database)

quote:

---

Maybe its possible to use a MS Access front end with an SQL database? not sure if that is possible..

---

I believe this is possible, and there are serveral embedable sql database out there. See google.

quote:

---

quote:and uploading larger dumps via php is an annoyance at best
Hmm, is this a php issue or a server timeout issue? If my access database got to 15mb or something would i have trouble uploading it? The database isnt likely to get anywhere near this big for only 500 items i guess without a front end..

---

Depends - uploading via ftp won't ever be a problem. But uploading directly from your client to a php script might be unsafe and 'painfull'.

quote:

---

Im assuming worst case i would have to rewrite the queries and some code with sql syntax - but not recreate the tables themselves?

---

Technically, you'll need to 'recreate' the tables - you'll be able to export the access data into a format your sql database can read. Might require some 'fiddeling' though.

quote:

---

Hmm this could possibly make a great FAQ on this topic?

---

Great - write it.

Thanks Tyberius, ill look into a bit more detail on some topics then rewrite it as a FAQ for the asylum to use if you all want . Now that i have the Jist of it all time to buckle down and do some good reading!

I wonder if there is a way to transfer via ftp, but via some code rather than a protocol. Just want it to be easy for the user. Ideally it would be great to have this in the front end, they just click their upload button and MS Access connects to the ftp and uploads it. Mmm something too look into


Thanks again!

well - I don't know about ms access, but most high level scripting languages like python,perl, etc. do offer pre made ftp client apis, which are easy enough to code for.

Ok i'll have to look into it, i'll be playing with it over the next few weeks so i'll post back with what i find out .

Might to some tests too and check out some speeds if i can T.T

Just an update for anyone thats interested:

You can code some VB into your ms access database allowing you to connect to an ftp, give you the ability to upload/download/rename etc files straight from your ms access database!

In my opinion this is a great option because it allows you to modify your database offline/update your products etc - then upload the database straight from there to the ftp.. This means you dont need to worry about a secure login page - which even if it is secure, its more secure if it doesnt exist .

So some Advantages
- More security
- More flexibility with your front end since its on the clients pc u can do just about anything which can speed up there data input etc.
- You dont have to wait each time you make a change, only once at the end.

Disadvantages
- You dont have roaming flexibility, allowing you to update it from anywhere in the world. Just depends if you need it... IF you do, you can always setup a secure section allowing you to download the front end to wherever you are, so you can make changes.

The only thing i have to work out now, is if i can upload the database that i currently have open :P.. I dont think its a problem as you can copy a database even while people are in it, so you dont have to close it first.

Yes yes i am becoming quite a fan of MS Access the more i work with it, its actually very flexible. Im not sure if mySQL is quite as flexible as i havn't had a chance to use it. It would be great to get all the benefits of mySQL /speed/security - with the flexibility of MS Access.

Having said that, im sure you can write a little bit of code to create a mySQL database from your MS Access tables - which would be the files it actually uploads.

Another thing i was thinking you could do, at the stage where you upload your database, rather then doing this you could automatically create your static files from the database which it uploads to the server, so you dont run off a database at all.

As said in another thread, this can be a major speed increase to your site and you dont have to worry about the number of users on your database. All you have to worry about is how many static files you actually have in a directory.

So basically you are cutting out the process of database -> php -> static that the user does when they access the site, and going straight to -> static.

Logic says - it has to be quicker.

What does everyone thing of this? Is it worthwhile doing? Do you see any issues?

Tyberius what do you think?