This more of a SQL question rather than Excel but I figure someone here could provide the light I need!
I'm using an Excel workbook as a small database that I am querying with ADO. And I've run into a database design problem.
I need to store multiple foreign keys in one field and I'm not sure how to best do this.
I should clarify that last sentence by means of example. Say the database is storing music data.
Table "tblSongs" has the following columns (so far):
- Id
- FK_Title
- FK_Artist
- FK_Genre
Where the last three columns are storing foreign keys to other tables in the database ("tblTitle", "tblArtist" & "tblGenre" respectively)
The FK_Title is easy. It stores one foreign key per record and this FK links to the PK column in the table "tblTitle" (i.e. Each Song can only have one Title)
But I'm going crazy on the next two columns.
A Song could have one or multiple artists (e.g. duets)
A Song could be classed under one or multiple genres
So how do I best store multiple foreign keys per field in the last two columns?
I have considered separators but how would I write the SQL query to return all instances of a particular key in that column?
Bookmarks