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

Groups > comp.databases.postgresql > #863

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

Show all headers | View raw

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

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

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

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

bill

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