Which Database

for a large (to me) database

[old_expat]

The project we are contemplating has been *sort of* designed as having a mySQL database.

It has 9 tables, and could have as many as 25,000 rows.

Primary table would have about 40 columns. Each row would be approximately 10 k (mostly in text description).

Most transaction write new data into certain tables.

I have recently read about some potential problems / housekeeping requirements when lots of writing is one to mySQL tables.

We may not be able to aford the license fee for commercial databases .. not sure how much a single license is ..

mySQL, postgres, or?

[Easy_Coder]

most shared hosting environments will come to you for a small monthly fee... like under $20 and you won't have to pay for licensing.

[topr8]

25,000 rows is tiny by db standards.

although 40 columns to one table is a lot, sure you can't normalize it a bit?

constant writing is not as big an issue as updating.

if your site isn't very busy you'll be fine with mySQl any shared host should provide it as part of the package.

[johnhh]

I agree with topr8

25,000 rows is nothing in database terms.

You should be OK with mysql. Often comes free anyway with some hosting companies and there are windows interfaces available if you get lost with command prompts.

[dba_guy]

Writing to large tables isn't normally a problem...unless you start expanding your table horizontally. In most circumstances, you will be fine, but this isn't the best design and can lead to slower inserts. Make sure you don't over index the table because this will definitely slow down your inserts. But if this table is also used for reporting/displaying data, it needs to be indexed and this is why you prefer having smaller, vertical tables and joining them.

[old_expat]

Thanks guys,

hosting - yes, our hosting is on our own VPS and does have mySQL available, and we could always install postgres, I assume

writing to tables - we won't be changing the table width (columns) on rewrite. Rewriting will be changes in cms, billing, notification, activation, deactivation, changing admin data, registration of users, registration of partners, changing of data for each

tables - we already have 9 tables in the design. Some are linked .. 2 sets of ID numbers, (one for B2B, one for C2B)

most queries will be searches from the "main table" which has data inserted from our own version of a simple cms plus other administrative data

second busiest table (we hope) will be the billing increment (very small amounts) .. deducting from a prepaid amount

The database is a little complicated (by our standards) because is is B2B plus C2B plus CMS plus Automated Billing

[zCat]

As others have said, that's not a particularly big database.

If you're storing criticial data (billing etc) in it, you might need to be extra vigilant with your design / coding if you're using MySQL, as it has a nasty habit of not-quite-doing-what-you-expect, e.g. you enter data which is longer than the field, and the data is silently truncated; or you create a foreign key, but the server doesn't have InnoDB activated, but still says it created the key.

There's a "MySQL gotchas" page out there which list this stuff.

[old_expat]

Hi zCat,

"MySQL, as it has a nasty habit of not - quite - doing - what - you - expect ...

There's a "MySQL gotchas" page out there which list this stuff. "

So is postgres a better choice for these apps?

[FalseDawn]

Look at the projected physical size of the tables as well as the number of rows.

25,000 rows is fine if you've got a handful of rows, a hundred bytes per row say.

But 10K per row? 25,000 rows equals 250MB, and that's not a trivial size - trying to run that on a shared server may well get you booted for excessive use of resources if you expect a lot of database activity.

[old_expat]

Hello FalseDawn,

I have a VPS. My primary concern is the integrity of the site and making sure everything* works every* time.

*99.9%?

[FalseDawn]

If by "integrity", you are referring to referential integrity, you will need to look at a more robust solution than MySQL, although it really does depend on exactly what the actual usage of the database is going to be in terms of transactional volume and concurrency levels.
MySQL is a great lightweight RDBMS, but ask too much of it and you might occassionally get corrupted indexes, or orphaned data (if you use an engine that doesn't support foreign keys, like MyIsam)

If your database usage is light, MySQL will be fine, but keep an eye on its size and do some research in tweaking it, which you'll need to do as it grows. Also consider using InnoDB engine - it has more overhead but supports foreign key referential integrity.