Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

Re: OT: SQL and me

by Lady_Aleena (Deacon)
on Oct 07, 2013 at 17:59 UTC ( #1057290=note: print w/ replies, xml ) Need Help??


in reply to OT: SQL and me

Most of my data is processed to be displayed on the web, though in case of catastrophe I can print out the relevant data for the insurance company. My goal is to find an easier way to store, add to, edit, and access my data. My data is currently a mixed hierarchical and tabular design. From what I am seeing here, SQL is just tabular with a lot of repetition. (From the example above, I would be putting all artists' albums in one jumbled table instead of individual artists' albums tables such as "Duran Duran's albums" or "Sting's albums".) My goal is to find a data storage system which has fewer files to maintain like a music file and a movie file (my movie data is ten times more of a headache than my music data).

If you are very interested, you can crawl all over my data to see how I am storing it now. In some spots, it is a jumbled mess. Do you think my data could ever fit into SQL tables?

Thank you all for dropping by and giving me something to look at and think about.

No matter how hysterical I get, my problems are not time sensitive. So, relax, have a cookie, and a very nice day!
Lady Aleena


Comment on Re: OT: SQL and me
Re^2: OT: SQL and me
by chromatic (Archbishop) on Oct 07, 2013 at 23:07 UTC
    From the example above, I would be putting all artists' albums in one jumbled table instead of individual artists' albums tables such as "Duran Duran's albums" or "Sting's albums".

    That's true, but you get some flexibility by doing that. To start with, you now have a single, uniform representation of all of the data. You know that every album is in the album table, for example. You also get a query mechanism that lets you perform ad hoc queries you don't have to plan in advance, as long as your data fits the relational model. For example, to get the titles of all albums released in the 90s, you could write:

    SELECT title FROM album WHERE release_year >= 1990 AND release_year <= 1999;

    Or just Sting albums from the '90s:

    SELECT title FROM album JOIN artist USING(album_id) WHERE release_year >= 1990 AND release_year <= 1999 AND artist.name = 'Sting';

    Or Sting and the Police albums:

    SELECT title FROM album JOIN artist USING(album_id) WHERE artist.name = 'Sting' OR artist.name = 'The Police';
Re^2: OT: SQL and me
by talexb (Canon) on Oct 08, 2013 at 02:56 UTC

    You can certainly store hierarchical data in a database, but it is a bit more complicated.

    A simple example is a family tree -- each person entry would have two other people as their parent. These people would all be in the same table, so you'd have to do a join back on the table that you're selecting from.

    Alex / talexb / Toronto

    Thanks PJ. We owe you so much. Groklaw -- RIP -- 2003 to 2013.

Re^2: OT: SQL and me
by Anonymous Monk on Oct 17, 2013 at 21:09 UTC
    I'll just give one warning about editing: unless you write a GUI interface for every type of data you have [1], it's pretty tough to actually edit single entries. It gets really tedious to type out "UPDATE sometable SET somecol = 'something' WHERE uniquelyidentifyingcondition" every time you find a typo.

    You probably want a GUI for adding entries, too. But the initial inserts can be done with a bulk loader written in Perl. The bulk loading is the easiest part.

    [1]: web frameworks with a 'scaffolding' feature help immensely here.

Re^2: OT: SQL and me
by wjw (Deacon) on Oct 18, 2013 at 09:48 UTC
    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...


    ...the majority is always wrong, and always the last to know about it...
    Insanity: Doing the same thing over and over again and expecting different results.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1057290]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (11)
As of 2014-11-28 16:13 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My preferred Perl binaries come from:














    Results (198 votes), past polls