|Problems? Is your data what you think it is?|
I am newish to SQL; I have not chosen a DBMS yet. People here and elsewhere have been beating me over the head with SQL. (GrandFather wrote RFC: Databases made easy over two years ago with me in mind, but it went right over my head.) I currently use .txt files to store my data. Most of the files are pipe separated values. (I do not like using commas since some values I store have commas in them and quoting would take up way too much disk space.) Going from .txt files which I can easily parse with a Perl function I have written specifically for my files to a format which I would have to use Perl modules written for more advanced users is very daunting. Not only would I have to figure out how to get my data into SQL files; I would have to figure out a way to get it back out of them. There is also the fear of typos in the data I currently do not know how to fix. (With my .txt files, fixing a typo means opening the file in my trusty text editor, I can not do that with SQL.)
I first started learning Perl to help get the size of my website under control back when I only had 10MB of space for it. Storing my data in the smallest space possible was a priority (hence pipes instead of commas so I would not have to quote my values). When I finally got more space, I was already neck deep in the way I am currently storing my data. I have a lot of little .txt files with little pieces of related data (across 200+ of directories and 800+ files in one case).
Since I do not know SQL, I don't know how to visualize the data structure it creates. I am familiar with software like Excel and less familiar with Access. (Access is a pain to set up which is why I avoid using it.) So, is SQL a big single table or tables within tables (within tables)? I have data I can experiment with which has a bit of complexity which I am unsure SQL can do, but SQL might be able to handle my eccentricities.
For my music database I am thinking of creating, there will be a table for "artists" with three fields for the name: "first name", "last name", and "band". Only one field will have to have an entry since if the artist is a band, there will be no first or last name. Also there are artists with only one name (usually first). However I already have a problem since some albums have various artists who I do not want to list individually, and with the above fields I do not have a place to put "various artists" really. Soundtracks also break the above. Do I create a fourth field called "special"?
Some artists I like I have not bought any albums, so I won't have a problem there. For the artists for whom I have bought albums, there will be another field which will be a table called "albums". Each album will have "title", "year", and "format". Even here there is a problem, some "albums" are 45s which do not have a singular name just the names of the songs on the 45. Format is an array since I own some albums in multiple media types (lp, cassette, or cd and one day straight digital). There is one case where an album is shared between two artists (The Police and Sting who will have separate entries under artists).
So I am wondering if SQL can do what I want, do I have to change my thinking, how do I set it up if it can, how hard will the code be to write, will it allow me to break up some values which I currently have lumped together, and some thoughts which are ambiguous.
I wish years ago when I was first starting to store my data in separate files, someone would have grabbed me and said "This is SQL, all the big kids use it; and if you want to play with the big kids, you need to use it too." I was a tad more pliant then.
Have a cookie and a very nice day!