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 15:48:08 -0400 Lines: 55 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit X-Trace: individual.net kfF5sKB/5kKqzYmYNcK5vw5oBV1a+Dvb5SIfSnxDDduSndmFus Cancel-Lock: sha1:4Ierch4fbn3y6lkERY2ZhtNF+KE= 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:863 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