Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

comment on

( [id://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

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



  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or How to display code and escape characters are good places to start.
Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others imbibing at the Monastery: (5)
As of 2024-03-29 00:39 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found