databases again
Jun. 25th, 2002 01:12 pm![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
memevectorThanks to the various people who responded to my earlier entry about MySQL. I haven't actually asked anyone anything yet 'cause I started reading lots of stuff on usenet instead. But it's nice to know you're there :-)
![[livejournal.com profile]](https://www.dreamwidth.org/img/external/lj-userinfo.gif)
ciphergoth asked in that thread
Any particular reason why MySQL rather than PostgreSQL?
Yes, a circumstantial/historical one, though I'm open to arguments about whether it's a good enough one.
I'm currently using DomainCity to host uncharted-worlds.org, and for not very much money I could get a standard package of theirs including MySQL. Admittedly, as they are exceptionally helpful and continually developing new techie bits, they might start running PostgreSQL as well if I asked them, but I hadn't actually thought of that till this moment :-)
<background>
One of the things I want to do in future is to incorporate a database into the ordering process for selling my music on the web. Not to create the pages - those (I currently expect) would still be static HTML, 'cause there's not really anything frequently-variable about them. And not to interface directly with my main customer database - that would provoke additional data-matching and security issues which i.m.o. aren't worth the trouble at the currently-likely scale. But just to accumulate orders so that they can then be processed in a batch rather than one by one.
</background>
I suppose the other main factor influencing me was the abundance of resources about MySQL and PHP. I'm a reasonable programmer and I've used databases a lot (not doing anything too arcane), but most of my software knowledge is pretty out of date. I left university in 1985 and it was all different back then :-) So I wanted something where both tutorials and free advice were gonna be abundant (which I imagine may be sufficiently true for PostgreSQL as well). But the DomainCity package, and being happy with their service, was the main reason.
Funnily enough, only the other day I was reading a discussion (I think it was on alt.php.sql) on the relative merits of MySQL and PostgreSQL. And I said to a friend (who's on a course about this stuff at the moment & might be doing some of the implementation for me) "Hmm, I wonder if we should be using PostgreSQL for this instead, maybe we should check it out" & they said "But weren't you using MySQL because DomainCity had it?" and I went "duh! oh yeah so I was!"
I had a vague sense from the usenet discussion that
a) PostgreSQL is better, at least in some ways, but
b) The differences probably won't matter for what I want to do.
But I could be wrong about (b), because I'm only at a fairly big-picture stage of working out what I want to do (and I haven't even started playing with MySQL yet), and I know I haven't grasped the implications of what the differences are between the two. So if you're in a position to summarise "circumstances in which I might be sorry" :-) , that would be v helpful. I could still change my mind and go looking for somewhere to run PostgreSQL if it turned out to be more appropriate.
Or I could use MySQL at the server and still have PgSQL on my PC (if it runs on PC). But then I've got two new learning curves instead of one.
<more background>
Currently my biggest tables are maybe 1000 to 2000 records, and the most I can imagine any of them needing to be is a few tens of thousands. (larger than that would imply either doing different stuff that I don't do now, or a scale of business that would imply me being rather rich from album sales, at which point I could think again, or indeed pay someone else to think again :-) )
The bit on the server would never reach more than a few hundred records max: because what it represents is a backlog of orders, so the more often people were ordering, the more often it would be collected and processed, just in the natural nature of things. (In fact it would probably usually be far fewer, because the only big peak is just after an album comes out, and you'd never let it accumulate for more than about a week because of timely fulfilling of the orders. I mean, even a few hundred is on the principle of "not expected, but plan for it being bucketloads, just in case" :-) )
That part may only be one flat file, with one record per order - I'm not sure yet, it depends partly on how we decide to manage the later incorporation of that data into the main database, which does use relational tables.
Most of what I ever do with any of my databases is either simply updating them manually, or mail-merge-ish things (e.g. printouts of charts, as well as actual mailshots), or occasionally summing subsections for accounts.
I seem to remember that the main limitation I ran into with Paradox was you could only have referential integrity & cascading "one table deep". (might be slightly misremembering what the issue was there, but it was something to do with wanting a record to refer to multiple records each of which then referred to multiple records... I think... memory getting hazy on that now, it was a long time ago.) Other than that, it was adequate (and even that was something I must have worked round). Can't think of anything else that showed up as even remotely "pushing the limits of what's possible with this software", except some non-critical graphical stuff to do with buttons on forms.
In fact I'd probably still be using it now if it hadn't fallen over and refused to reinstall (probably due to corruption of ancient floppies from the olden days, i.e. 1994).
</background>
what do you think of all that then? opinions welcome :-)
ciphergoth asked in that threadAny particular reason why MySQL rather than PostgreSQL?
Yes, a circumstantial/historical one, though I'm open to arguments about whether it's a good enough one.
I'm currently using DomainCity to host uncharted-worlds.org, and for not very much money I could get a standard package of theirs including MySQL. Admittedly, as they are exceptionally helpful and continually developing new techie bits, they might start running PostgreSQL as well if I asked them, but I hadn't actually thought of that till this moment :-)
<background>
One of the things I want to do in future is to incorporate a database into the ordering process for selling my music on the web. Not to create the pages - those (I currently expect) would still be static HTML, 'cause there's not really anything frequently-variable about them. And not to interface directly with my main customer database - that would provoke additional data-matching and security issues which i.m.o. aren't worth the trouble at the currently-likely scale. But just to accumulate orders so that they can then be processed in a batch rather than one by one.
</background>
I suppose the other main factor influencing me was the abundance of resources about MySQL and PHP. I'm a reasonable programmer and I've used databases a lot (not doing anything too arcane), but most of my software knowledge is pretty out of date. I left university in 1985 and it was all different back then :-) So I wanted something where both tutorials and free advice were gonna be abundant (which I imagine may be sufficiently true for PostgreSQL as well). But the DomainCity package, and being happy with their service, was the main reason.
Funnily enough, only the other day I was reading a discussion (I think it was on alt.php.sql) on the relative merits of MySQL and PostgreSQL. And I said to a friend (who's on a course about this stuff at the moment & might be doing some of the implementation for me) "Hmm, I wonder if we should be using PostgreSQL for this instead, maybe we should check it out" & they said "But weren't you using MySQL because DomainCity had it?" and I went "duh! oh yeah so I was!"
I had a vague sense from the usenet discussion that
a) PostgreSQL is better, at least in some ways, but
b) The differences probably won't matter for what I want to do.
But I could be wrong about (b), because I'm only at a fairly big-picture stage of working out what I want to do (and I haven't even started playing with MySQL yet), and I know I haven't grasped the implications of what the differences are between the two. So if you're in a position to summarise "circumstances in which I might be sorry" :-) , that would be v helpful. I could still change my mind and go looking for somewhere to run PostgreSQL if it turned out to be more appropriate.
Or I could use MySQL at the server and still have PgSQL on my PC (if it runs on PC). But then I've got two new learning curves instead of one.
<more background>
Currently my biggest tables are maybe 1000 to 2000 records, and the most I can imagine any of them needing to be is a few tens of thousands. (larger than that would imply either doing different stuff that I don't do now, or a scale of business that would imply me being rather rich from album sales, at which point I could think again, or indeed pay someone else to think again :-) )
The bit on the server would never reach more than a few hundred records max: because what it represents is a backlog of orders, so the more often people were ordering, the more often it would be collected and processed, just in the natural nature of things. (In fact it would probably usually be far fewer, because the only big peak is just after an album comes out, and you'd never let it accumulate for more than about a week because of timely fulfilling of the orders. I mean, even a few hundred is on the principle of "not expected, but plan for it being bucketloads, just in case" :-) )
That part may only be one flat file, with one record per order - I'm not sure yet, it depends partly on how we decide to manage the later incorporation of that data into the main database, which does use relational tables.
Most of what I ever do with any of my databases is either simply updating them manually, or mail-merge-ish things (e.g. printouts of charts, as well as actual mailshots), or occasionally summing subsections for accounts.
I seem to remember that the main limitation I ran into with Paradox was you could only have referential integrity & cascading "one table deep". (might be slightly misremembering what the issue was there, but it was something to do with wanting a record to refer to multiple records each of which then referred to multiple records... I think... memory getting hazy on that now, it was a long time ago.) Other than that, it was adequate (and even that was something I must have worked round). Can't think of anything else that showed up as even remotely "pushing the limits of what's possible with this software", except some non-critical graphical stuff to do with buttons on forms.
In fact I'd probably still be using it now if it hadn't fallen over and refused to reinstall (probably due to corruption of ancient floppies from the olden days, i.e. 1994).
</background>
what do you think of all that then? opinions welcome :-)
