Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #862

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

Show all headers | View raw

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.)  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.

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.

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