Path: csiph.com!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail From: Bill Gunshannon Newsgroups: comp.databases.postgresql Subject: Re: Table with a variable number of elements in a column Date: Sat, 27 Apr 2019 16:29:47 -0400 Lines: 101 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit X-Trace: individual.net 4DWA5ViCyeyxw/x2ZM9ktwVkYvfDvjLh+nsLk7ozxxKBVbaPdd Cancel-Lock: sha1:JSuD9EUC/eSmHL01e0uiu14rNnk= User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.5.1 In-Reply-To: Content-Language: en-US Xref: csiph.com comp.databases.postgresql:865 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