Databases
StackOverflow2013
Postsdbo
POWhat is the best way to access data in a normalized database schema?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhat is the best way to access data in a normalized database schema?
    primarykey
    data
    text
    <p>I've got a problem that keeps coming up with normalized databases and was looking for the best solution.</p> <p>Suppose I've got an album information database. I want to setup the schema in a normalized fashion, so I setup two tables - albums, which has one listing for each album, and songs, which lists all songs contained by albums.</p> <pre><code>albums ------ aid name songs ----- aid sid length </code></pre> <p>This setup is good for storing the data in a normalized fashion, as an album can contain any number of songs. However, accessing the data in an intuitive manner has now become a lot more difficult. A query which only grabs the information on a single album is simple, but how do you grab multiple albums at once in a single query?</p> <p>Thus far, the best answer I have come up with is grouping by aid and converting the songs information as arrays. For example, the result would look something like this:</p> <pre><code>aid, sids, lengths 1, [1, 2], [1:04, 5:45] 2, [3, 4, 5], [3:30, 4:30, 5:30] </code></pre> <p>When I want to work with the data, I have to then parse the sids and lengths, which seems a pointless exercise: I'm making the db concatenate a bunch of values just to separate them later.</p> <p>My question: What is the best way to access a database with this sort of schema? Am I stuck with multiple arrays? Should I store the entirety of a song's information in an object and then those songs into a single array, instead of having multiple arrays? Or is there a way of adding an arbitrary number of columns to the resultset (sort of an infinite-join), to accommodate N number of songs? I'm open to any ideas on how to best access the data.</p> <p>I'm also concerned about efficiency, as these queries will be run often.</p> <p>If it makes any difference, I'm using a PostgreSQL db along with a PHP front-end.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USDaniel Lew
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWhat is the best way to access data in a normalized database schema?
      1. USTony
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload