Re^2: OT: SQL and me

[wjw]

Frankly, after looking at your data, SQL is exactly the way that I would go.

If your goal is simply to come up with a solution and not spend much effort at it, then organizing what you have in spread sheets or text files, or in a Google Doc spreadsheet is probably good enough. You can make a nice form for new entries and as a result you will even have a pretty decent GUI to help you maintain your information.

If, on the other hand, you are looking to learn SQL, then your project is an excellent one to accomplish that with.

I had mentioned in a previous post that SQLite might be a good option for you, and I stand by that. The process of creating the DB will force you to make decisions about what you really want from this project. Additionally, making a web interface (GUI) for SQLite is not much different than doing so for any other DB. You get the advantage of learning basic SQL, its integration with Perl via DBI/DBD and the UI via CGI etc... .

Another advantage is that SQLite is a single file DB, which means you can move it around from system to system(Windows, *nix etc...), back everything up in one single step(copy the file to somewhere safe). You mention 'fewer file to move around', there would only be one in this case.

You also mention movie information. You can decide whether you want two separate DB's, one for music and one for movies, or simply combine them in the same DB, again to ease keeping track of stuff(I would use one DB because there is the possibility of correlating musicians and music to movies which can be done across db's, but it is more to keep track of).

The project gets broken into a few basic parts:

• Data storage design
• Data storage development(create the db)
• Mass import(of existing data) including cleanup and prep
• GUI design for maintenance, updated, and display
• GUI devel

The design of GUI and DB are fairly closely coupled initially. If you start by listing what you expect to do at the GUI, you will end up with a good rough db design which you can normalize when you focus on the DB design.

For example:

• I need to be able to add Band names
• I want to add members to bands
• I want albums to associate with Bands and Members
• I want songs associated with Bands, Members, formats, albums
• ....

The requirements you state about what you want imply a DB to me. Whether you use a DB, or use cvs files or a spreadsheet, the functionality you describe is what a DB is really good for.

As a very rough start, you could take the following and load it into sqlite(free download I am sure you can find for your env) to give yourself a feel for what you might want to do if you go the DB route.

CREATE TABLE "Groups" (
    "eid" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    "name" TEXT
);
CREATE TABLE songs (
    "eid" INTEGER NOT NULL,
    "title" TEXT,
    "album_id" INTEGER,
    "artist_id" INTEGER
, "year" TEXT);
CREATE TABLE "Albums" (
    "eid" INTEGER PRIMARY KEY AUTOINCREMENT,
    "title" TEXT,
    "year" TEXT,
    "format" TEXT
);
CREATE TABLE artists (
    "eid" INTEGER,
    "first_name" TEXT
, "last_name" TEXT, "moniker" TEXT);

Best of luck...

---

In Section Meditations