Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic

Re: OT: SQL and me

by aaron_baugher (Curate)
on Sep 27, 2013 at 20:24 UTC ( #1056072=note: print w/replies, xml ) Need Help??

in reply to OT: SQL and me

In short, yes, a SQL database will do the things you're talking about here. In a typical database, you have a group of tables. Each table is made up of some number of records, each containing the same named fields. So your artists table might look something like this:

CREATE TABLE artists ( first_name text default NULL, last_name text default NULL, band text default NULL, id int(5) NOT NULL auto_increment );

That creates a table where each record has four fields, named 'first_name', 'last_name', 'band', and 'id'. The first three contain up to a certain limit of text (depending on your DBMS and OS), and can be empty (NULL). The last one contains an integer of up to 5 digits, cannot be empty, and will automatically be filled with the next highest integer for that field if you don't provide one. So once you create this table, you can start adding artists:

INSERT INTO artists (first_name, last_name) VALUES ('Billy','Joel'); INSERT INTO artists (band) VALUES ('AC/DC');

Now, the other important thing is that your tables can (and usually do) have relationships. That's why you'll often see the term RDBMS -- the R stands for relational. One of the most important things in designing your database is determining the relationships between your tables. It may be one-to-many, many-to-one, or many-to-many. Let's say your album table looks like this:

CREATE TABLE albums ( name text NOT NULL, year int(4) default NULL, format enum('lp','cassette','cd','45'), artist int(5) default NULL, id int(8) NOT NULL auto_increment );

Now each album must have a name and can also have a publication year. It has a format which must be one of the possibilities allowed by enum(), and it also has an id which is an integer of up to 8 digits that is auto-incremented. It can also have an artist value -- but instead of having the artist's full name and information, this contains an integer which corresponds to an id of one of the records in your artists table. You now have a one-to-many relationship, because one artist can be assigned to many albums. If your artists table grows to contain numerous fields, that one id will connect all that info to each album it's assigned to. Now if you look at your artists table and see that Billy Joel has ID 123 and AC/DC has ID 456, you can add some albums:

INSERT INTO albums (name,year,format,artist) values ('Glass Houses','1 +980','lp',123); INSERT INTO albums (name,format,artist) VALUES ('Piano Man','45', +123); INSERT INTO albums (name,year,format,artist) VALUES ('Back in Black',' +1980','cd',456);

Of course, this is very simplified compared to what you'd need for a real music database. You'd want a lot more flexibility in what can be entered for different fields. Format might become its own table, for instance, so you can easily add formats and index them by ID. You'd probably want a many-to-many relationship between artists and albums, so that an artist could have multiple albums and an album could have multiple artists. That requires a third table to track the connections between the two, so that's getting a little more advanced. All doable with some practice, though.

Aaron B.
Available for small or large Perl jobs; see my home node.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1056072]
[stevieb]: ...has honed in my skills of recognizing sound
[stevieb]: All of the early members are coming out of the woodwork today :) Hey, planetscape
[Corion]: This cover version had so much promise but the singing is underwhelming :-/
[stevieb]: erix Thanks! I dislike remakes of songs usually, but my favourite remix of guitar/weeps is Jeff Healey. I'll take a listen to yours
[planetscape]: hi stevieb!
[stonecolddevin]: also hi Corion
[Corion]: stevieb: Yeah, but I don't follow guitar players enough... I never got Prince and I don't think I could recognize his play style. I can recognize Santana and Nile Rodgers, but that's about it, and not really deep knowledge I think ;)
[stevieb]: Jeff Healey cut of My guitar gently weeps
[stonecolddevin]: you don't recognize Prince. Prince tells you it's him.
[erix]: Alvin Lee (Ten Years After) played a jazzguitar ("Big Red"), a good warm sound

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (6)
As of 2017-06-22 21:33 GMT
Find Nodes?
    Voting Booth?
    How many monitors do you use while coding?

    Results (531 votes). Check out past polls.