Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things

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]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (6)
As of 2018-06-24 20:50 GMT
Find Nodes?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?

    Results (126 votes). Check out past polls.