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

Groups > comp.databases.postgresql > #865

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

Show all headers | View raw

On 4/27/19 4:02 PM, John-Paul Stewart wrote:
> 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.

Ah yes, I guess I was too tied into the database using the number
to determine how many fields it needed.

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

I got that now.
But I guess I am still stuck with how I define how many tracks columns
there has to be for each album.  I think I have been away from this for
too long.  I am missing something that is probably both simple and very
apparent.

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

Got it, I understand that part now.  Like I said, been away from this
for much too long.  (Damn retirement.....)

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

I will.  Amazing how long it takes to learn and how fast you can
lose the learning when you stop doing it.

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