Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2013
    Posts
    3

    How To Design A Folio List?

    I'm just after the basic concept for good database design.

    'A Folio List' might not be a good name - I just couldn't think of what else to call it. I just mean a list of paper pages with the real data being their content.

    What I've got is many pages, about 500, bound in loose leaf binders.

    They have sheet music on them.

    Some pages have more than one piece of music, more than one title.

    And some titles have more than one page.

    So do I:

    1. Give unique page number and have maybe 6 fields for up to 6 titles per page, effectively listing pages or
    2. Give unique numbers to each title and effectively list titles or
    3. Have two tables with a many to many relationship - 'pages' to 'titles' Meaning three tables finally.

    Point being, of course, it's virtually impossible that there'd be more than say six titles per page - to get six we'd be into something only half a dozen bars long like perhaps bugle calls. I realise the two tables is the classic relational database approach but real world, pragmatic, would it be the way to go here and now for me, for this application.

    At the moment I've just numbered each page and am busy copying the details of which titles exist on which pages into a excel spreadsheet.

    The 'two tables' approach doesn't work with Excel ( I suppose, though I wouldn't be surprised to find Excel experts indignantly claiming it's a snack to implement) but I'm proposing to stick it all into MySQL when I get to that stage.
    Last edited by abrogard; 09-12-13 at 21:53.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    So, each of the pages has a unique number, from 1 to about 500?
    Was there any method used in deciding how they would be sorted?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2013
    Posts
    3
    They were casually sorted in the physical folder this data is a representation of.

    Lumped together something like: jazz standards, Australian Folk, children's songs, classical.

    And, yes, each page with a unique number.

    When I get this organised the list has the potential to expand to maybe 2000 folios which would represent all the sheet music I have including that which exists only in the computer at the moment.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I'd start with five tables: Pages, Titles, Genres, TitleOnPage, and TitleInGenre. Each table tracks one kind of thing...

    The tables that track physical things are simple to imagine... Pages keeps track of information about the pages like their number, date cataloged, size, condition (new/worn/whatever), etc. Titles keeps track of the information about each title (and keep in mind that very different songs may share a title). Genres keeps track of information that you want/need to track on each genre.

    The "relationship" tables serve to allow you to connect zero-to-many of one thing with zero-to-many of another thing. Since a Title can appear on many pages, there may be many TitleToPage rows for that title. Since a page can have many titles on it, there can be many TitleToPage rows for that page. When you insert a new row into a physical table, there will be no rows in the corresponding relationship tables for it (yet) which is how an unmatched "zero" row is represented.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Sep 2013
    Posts
    3
    I'm really not wanting to build a database at all. I'm just indexing my sheet music because it's in a mess, both in the computer and in print.

    Can't find my way through the print, don't know what I've got there or when I do know it's there I don't know where....

    And in the computer the same.

    So I think you could say I'm just trying to index it all - right?

    So I'm thinking two many to many tables and a third joining table between them.

    Pages: Page Number, Date, Condition, WhichFolder.
    Music: Music ID, Title, Version, Composer, Lyricist, Time Sig, Key, Instrument, Source, Performer, Date.

    And the joining table: 'PagesMusic' with PageNumber, MusicID.

    I'm a bit uncertain about the Music table. Seems to me I need all those fields to establish it is a unique piece of music so does this mean I have to make a concatenated Primary Key of all those fields?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    You know your needs far better than anyone else, and what you've proposed seems like a sound design to me. I can't offer any opinion on whether the solution is "complete" or not, that's something that only you are able to do.

    I'd use and declare the Music ID as the PK (Primary Key), since it is easy to use and will uniquely identify every row in your Music table. The other attributes (Title, Version, Composer, Lyricist, Time Sig, Key, Instrument, Source, Performer, Date) of the Music table form an NK (Natural Key) that you ought to protect with a UNIQUE CONSTRAINT.

    Data Architects are usually pretty amiable sorts, the kind of folks that most people enjoy having around... If you want to start a ruckus in a group of them, ask about the difference between Natural Keys and Surrogate Keys, and which you should use. A riot will spontaneously erupt! For a number of reasons I recommend using and maintaining both Natural and Surrogate Keys, which means that everyone hates me with impunity!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by abrogard View Post
    I'm just after the basic concept for good database design.
    Quote Originally Posted by abrogard View Post
    I'm really not wanting to build a database at all.
    Quote Originally Posted by abrogard View Post
    So I'm thinking two many to many tables and a third joining table between them.
    This is where I quietly duck out of a thread....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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