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 SQLNot sure what the "foreign keys" have to do with it, but I understand
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 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.
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.)
Not sure what the "foreign keys" have to do with 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.
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.
On 4/27/19 4:02 PM, John-Paul Stewart wrote:
On 2019-04-27 3:48 p.m., Bill Gunshannon wrote:
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.
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.
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.
Not sure what the "foreign keys" have to do with 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.
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.
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".
On 2019-04-27 4:29 p.m., Bill Gunshannon wrote:
On 4/27/19 4:02 PM, John-Paul Stewart wrote:
On 2019-04-27 3:48 p.m., Bill Gunshannon wrote:
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.
By using a tracks table, it's no longer a question of "how many tracks columns" but "how many tracks rows" instead. And the answer is "however
many you populate with the same album id". That's the beauty of it.
Yeah, I went upstairs to watch the golf and think about it and
it hit me. I was looking at it all wrong.
Thank you very much for kicking me in the head to knock this stuff
loose so it could float back up to the surface.
I have a similar question/answer on StackExchange: https://softwareengineering.stackexchange.com/questions/405567/ how-to-use-strict-schema-with-seemingly-fluid-data-type
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 286 |
Nodes: | 16 (2 / 14) |
Uptime: | 82:53:39 |
Calls: | 6,495 |
Calls today: | 6 |
Files: | 12,096 |
Messages: | 5,276,825 |