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

Comment on

( #3333=superdoc: print w/replies, xml ) Need Help??

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.

In reply to Re: OT: SQL and me by aaron_baugher
in thread OT: SQL and me by Lady_Aleena

Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":

  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?

    What's my password?
    Create A New User
    [marto]: evening all
    [Happy-the-monk]: good evening!

    How do I use this? | Other CB clients
    Other Users?
    Others contemplating the Monastery: (7)
    As of 2017-11-18 18:23 GMT
    Find Nodes?
      Voting Booth?
      In order to be able to say "I know Perl", you must have:

      Results (277 votes). Check out past polls.