Groups | Search | Server Info | Keyboard shortcuts | Login | Register

Groups > comp.databases.postgresql > #864

Re: Table with a variable number of elements in a column

Show all headers | View raw

On 2019-04-27 3:48 p.m., Bill Gunshannon wrote:
> On 4/27/19 3:39 PM, John-Paul Stewart wrote:
>> On 2019-04-27 2:59 p.m., Bill Gunshannon wrote:
>>>
>>>
>>> Not sure what the right terminology for this is, but I will
>>> provide my example and see if anyone can tell me how I might
>>> do this.
>>>
>>> I want to create a database table for an index of all my record
>>> albums.
>>>
>>> The basic stuff is easy.
>>>
>>> Title, Artist, Publisher and the Publisher's ID # as a primary
>>> unique key.
>>> But then I get to the hard part.
>>> Number of tracks and then a list of those tracks.
>>> This would, obviously, be different and variable from album to album.
>>>
>>> So, I need a way to define a table that has a variable number of fields
>>> depending on the value in Number-of-Tracks.
>>
>> The usual way to do this in a relational database is to have a separate
>> table of tracks and define the relationship between the two tables.  For
>> example, add a unique album id number to your albums table.  (You won't
>> need the "number of tracks" field you propose.) 
> 
> Well, I am going to want that number anyway. :-)

Yes, but the database can calculate it for you, for each album.  Having
a separate column could lead to problems where the column says something
different than the actual number of tracks stored in the table.

>>                                                   Then have a tracks
>> table that has album id (which refers back to the albums table), track
>> number, track title, etc.  You can then use a JOIN clause in your SQL
>> SELECT statement to associate the track and album info with each other,
>> or other queries to get the number of tracks in an album (e.g., "select
>> count(*) from tracks where album_id = 1"), or whatever else you need to
>> know about it.
> 
> But if I understand this correctly I would need a separate table for
> every album resulting in, potentially, thousands of tables. (OK, in
> my case hundreds but others may like this idea, too, when I finish
> the whole project.)

No, it's only one "tracks" table.  That's why there's an album id column
in it:  so that each track is associated with one album.  There'd be
(album 1, track 1), (album 1, track 2), (album 2, track 1), and so
on....  Each (album, track) tuple would have to be unique.  But you'd
keep all the tracks for all the albums in one table.

>>
>> You probably want to read up on the concept of "foreign keys" and SQL
>> JOIN clauses.  That's the usual way to do it in a database, and a big
>> part of the relational model.
> Not sure what the "foreign keys" have to do with it, 

The foreign key (in this case, the use of album id in the tracks table)
is what connects the individual tracks to the albums, and ensures that
the album id actually exists.  By declaring album id (in the tracks
table) as a foreign key referring to the albums table, Postgres will
ensure that each track has a corresponding album.  Without declaring it
as a foreign key, you can mistakenly have a track who's album id doesn't
exist.

> but I understand
> the JOIN part.  I was just looking for an efficient way to do this and
> somehow the though of hundreds to thousands of TABLES scares me.

It's just two:  albums and tracks.

> But, thanks for the info.  I will likely end out trying it a couple
> of different ways before deciding which is best.

Another topic to read up on is "database normalization".

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Table with a variable number of elements in a column Bill Gunshannon <bill.gunshannon@gmail.com> - 2019-04-27 14:59 -0400
  Re: Table with a variable number of elements in a column John-Paul Stewart <jpstewart@sympatico.ca> - 2019-04-27 15:39 -0400
    Re: Table with a variable number of elements in a column Bill Gunshannon <bill.gunshannon@gmail.com> - 2019-04-27 15:48 -0400
      Re: Table with a variable number of elements in a column John-Paul Stewart <jpstewart@sympatico.ca> - 2019-04-27 16:02 -0400
        Re: Table with a variable number of elements in a column Bill Gunshannon <bill.gunshannon@gmail.com> - 2019-04-27 16:29 -0400
          Re: Table with a variable number of elements in a column John-Paul Stewart <jpstewart@sympatico.ca> - 2019-04-27 17:10 -0400
            Re: Table with a variable number of elements in a column Bill Gunshannon <bill.gunshannon@gmail.com> - 2019-04-27 18:40 -0400
              Re: Table with a variable number of elements in a column John-Paul Stewart <jpstewart@sympatico.ca> - 2019-04-27 19:26 -0400
  Re: Table with a variable number of elements in a column alexander.d.mills@gmail.com - 2020-02-21 14:02 -0800
    Re: Table with a variable number of elements in a column Christian Barthel <bch@online.de> - 2020-02-22 09:15 +0100

csiph-web