Databases!

Slime101 · 9 Jun 2006 at 12:34

I have a fairly limited understanding of databases, this is causing problems!!

I need to store some records, which when queried return results...

Within the DB i need to store (as an example) School(s), class(es), pupil(s), pupil age, and an ID number that relates to an image.

Fairly simple so far, but i dont know how the database design would work, it needs to be searchable by pretty much all of the fields above, and display the appropriate results, (pupilds in class or whatever - with their images from the server) however we dont want someone to be able to search for pupil name from the start because we dont want it to search 60,000 records in full looking for one child.

So - any suggestions or help would be appreciated.

Adz · 9 Jun 2006 at 12:39

Unless the traffic is going to be very heavy which it doesn't sound like it will (private access only?), there's no reason why you couldn't use a single table with appropriate indexes

.

Beansprout · 9 Jun 2006 at 13:33

That sounds pretty straightforward if you know SQL - but as it appears you don't I'd ask someone else in the IT department (presuming one exists)

Also, searching 60,000 records is no problem and if you add the correct indexes and query caching, MySQL (or any database) will fly

Slime101 · 9 Jun 2006 at 15:15

Adz said:
Unless the traffic is going to be very heavy which it doesn't sound like it will (private access only?), there's no reason why you couldn't use a single table with appropriate indexes .

Public access, its just that even with that many "users" you'll still only ever get a handful (upto a few hundred tops) on at any one time running a query.

Beansprout · 9 Jun 2006 at 15:17

On an average server with plenty of RAM, MySQL will handle a few hundred queries per second so honestly, you're not going to have any problems