+ Reply to Thread
Results 1 to 7 of 7

Excel Database/SQL question - multiple keys in one field?

  1. #1
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Question Excel Database/SQL question - multiple keys in one field?

    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?
    Last edited by mc84excel; 11-12-2014 at 05:10 PM.
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

    *******************************************************

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Excel Database/SQL question - multiple keys in one field?

    If you're truly looking for a database solution, abandon the "smart field" approach and set up a star or snowflake schema with normalized tables. There are probably some MS Access templates out there with structures you could pattern from.
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Excel Database/SQL question - multiple keys in one field?

    You don't store multiple keys, or values, in a field.

    I think you need to look into normalization and using 'junction' tables.
    If posting code please use code tags, see here.

  4. #4
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Excel Database/SQL question - multiple keys in one field?

    Thanks Norie. Upon your advice I have read up on Junction (aka Bridge) tables. But when I try to apply it to the real world, I find I'm back to my first question.

    Say I create a junction table "tblSongsGenre" and I replace FK_Genre in "tblSongs" with FK_SongsGenre (being the foreign key to the junction table)

    In the new table tblSongsGenre I have the following columns:
    • ID (just an auto number)
    • FK_Genre (being the foreign key to tblGenre)

    The values in FK_Genre will be storing multiple keys? Surely not. But then how do I associate multiple Genre IDs in one record of the junction table? (I think I'm missing something here)


    UPDATE: nvm. I think I've worked it out. I need a column for the FK to Songs in the junction table and I have 1 Genre FK per record. (So if there is multiple Genres for one song - this would be multiple records in the junction table)
    Last edited by mc84excel; 11-11-2014 at 10:53 PM. Reason: Solved

  5. #5
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Excel Database/SQL question - multiple keys in one field?

    You're making hard work of this by trying to do it in Excel, just download a database and have done with it.

    Yes, your update is correct, this is generally referred to as a Many to Many relationship

  6. #6
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Excel Database/SQL question - multiple keys in one field?

    Good to hear from you again Kyle

    Yes I know that using Excel workbook as a database is a pain in the neck but I have been doggedly persisting for a few reasons:
    • Because I have to build everything from the ground up, it forces me to think through the fundamental basics of database design
    • I like the fact that I can work on the file on various computers without the need to install any additional software

    I know what a Many to Many is but I didn't know what a Junction table was (My level of SQL training hasn't yet extended to designing databases)

  7. #7
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Excel Database/SQL question - multiple keys in one field?

    Ok, fair enough. I don't agree with point 1 though, that would be true regardless of whether you are using excel or a database

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 9
    Last Post: 05-22-2012, 08:59 AM
  2. DSUM database field across multiple ranges
    By fynn in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-18-2008, 06:34 AM
  3. Identifying Database field names thru Excel
    By BWM in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-06-2007, 02:11 PM
  4. [SOLVED] Can you Sparse a field in Excel??? and Database Query??
    By TotallyConfused in forum Excel General
    Replies: 3
    Last Post: 12-06-2005, 07:30 PM
  5. Import from Database using field from excel.
    By BD in forum Excel General
    Replies: 1
    Last Post: 05-10-2005, 06:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1