Re^3: Design flat files database

From what you described, I get the impression an SQL optimisation book is not what you need. Rather, try out whether a variant (e.g. SQLite, PostgreSQL) would suit your needs better (i.e. more easily, less painfully) than flat files do.

I don't know the scale nor expected use of your project, so it's hard to advise. SQL isn't that hard, yet it's powerful.

A friendship (ID) could have two user(ID)s as its members, for instance.
Top rated items would be selecting an x count of items sorted by their rating values in descending order.
Indexes are fairly standard too.

Optimising seems more for your flat files design, given that you have more experience there. Using a routine to generate paths from IDs (as suggested earlier) would be a good way to start a benchmark schemes vs filesystem flavour and operating loads you expect to see.

Comment on Re^3: Design flat files database

Replies are listed 'Best First'.
Re^4: Design flat files database by locked_user sundialsvc4 (Abbot) on Jul 16, 2011 at 11:24 UTC
Well, my own personal experience has led me to develop this one rune: “Do not, if possible, store BLOBs in an SQL database of any kind.” I know that this is an oversimplification; that there must be SQL databases that do this really well and that don’t become an insufferable management PITA. (As the Moody Blues song says, "I know they’re out there some-where ... some-where ... sommmme-where ...”) Whereas, I also know of another commonly-used type of database that is ideally suited for storing tens of millions of variable length records: it’s called “a file system,” and the records are called files. It’s not a good index, but it’s a great store. Knowing, as I do, that I am about to create perhaps millions of very similarly named files, and also knowing that this “concentration of weight in a small area” might create a PITA of a different sort for the filesystem or for me or both, I would create an additional tree-taxonomy in the form of a directory structure of my own choosing. But, frankly, I would not spend too much time searching for that “sweet spot.” I would hazard that now I am starting to try to meddle in the filesystem’s proper business, probably with no practical return on the time-and-effort investment. Instead, I would simply arrange things so that I can change things later, if I need to. A more interesting concern might be how to perhaps distribute the data among multiple physical volumes, by assigning different high-level directories to different physical volume-groups. (Assuming of course that I am not paying mega-bucks a month for a system like Oracle.) This taxonomy would let me do that.
Re^4: Design flat files database by AlfaProject (Beadle) on Jul 16, 2011 at 19:03 UTC
I'm learning now data modeling and I loved that relational database model But why postgresql and not mysql ? is it that bad that no one recommends it ?	[reply]
Re^5: Design flat files database by rkrieger (Friar) on Jul 16, 2011 at 21:07 UTC
People tend to have their favourites. Personally, I greatly prefer PostgreSQL as it gives me much less pain than MySQL does. I'm certain others will disagree with me. What matters is your requirements, preferences, etc. I strongly recommend trying things out for yourself to pick what fits your environment. If it's just to get a feel for SQL, SQLite may be the simplest start as it is contained within a single file. Once you're ready for multiple users, usage over the network, etc. try the documentation and see which choice would make your work easiest.	[reply]