Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2008
    Location
    Cleveland
    Posts
    4

    Unanswered: first DB design...

    have a small sheet music publishing company and I’m trying to create a system to do the following:
    1. Product info (I’ve created a table holding this info)
    2. Customer info (I’ve created a table holding this info)
    3. Keep track of product purchases
    Who bought it, when, for how much
    4. Calculate the total sales of each product item

    DB stuff is totally new to me and I’m not sure how to connect the info from 1 & 2 into 3 – or the best overall design if the DB for that matter…Obviously, I’d like to not re-enter customer info for each item they buy and I’d like to be able to query things like who has bought a certain item and when.

    I have learned some basics on ‘relationships’ and used that for common fields in my Products table
    Thanks for any guidance….

    derek

    ps
    i've created a flowchart of sorts to outline the charts that I think are needed. As this is my first db, any comments on what you see would be appreciated...ie. how to uses 'relationships' more, other things that I should read up on...don't really know what I'm doing
    Attached Thumbnails Attached Thumbnails Flow Chart.png  

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Correct those spelling errors. Payed = Paid

    You'll need another table between Orders and SheetMusic... get rid of that space in Sheet Music and that # symbol too.... The extra table could be called SALES. It needs OrderID, SheetMusicID, SalePrice and Qty. The primary key for this table is OrderID, SheetMusicID.

    Add CustomerID to your Order table and relate it to your Customer table.

    I don't know how Royalties fits in. I don't think I know enough about royalties to offer much there. I'd say it has to fit into your ordering system somewhere, but I haven't sussed out where yet.

    Good job on seeking advice before stumbling forward
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Oct 2008
    Location
    Cleveland
    Posts
    4
    Thanks Trekker, u are a nice guy!
    Sorry about the blasted spelling...that's why I'm a cellist

    So, I'm working through your suggestions in my mind and I think it's making sense.
    I want to be able to track that 'Joe Mama' purchased a copy of 'Elegy' and 'Polly' by Dave Brubeck, 'Cellocomojo' and 'The Return' by Stephen Katz. Then I need:

    1. the table keeping track of total sales of each title to be updated (should I add a field to the SheetMusic table or create a new table?)
    2. Royalties - this sale would mean that I owe 20% of the selling cost of each Brubeck item and 25% of the selling price for each Katz item. As royalties are paid once or twice a year, the sale and price would just be added to a growing sales record I suppose...


    thanks for the help, this is making my small brain tired!

    I've updated the attached chart for your perusal...
    Attached Thumbnails Attached Thumbnails Flow Chart.png  

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Hey, what are you designing those diagrams in? Is that the report printing tool in Access? Are those relationships enforced or don't they exist yet?

    1. You won't need it. The orders / sales tables track this detail and you can interrogate them to find total sales of items. Just the same as "Number Sold" that field can go bye byes. You can count the Qty of the Sales table to get that too.

    2. Sounds to me like all you need there is to record the 20% / 25% (RoyaltyCommission) in the Composers or SheetMusic tables, whichever is most appropriate. If the royalty commission is always 20% for Brubeck and the same applies to ALL composers, then it belongs in the Composers table. If there is any exception to that, then it belongs in the SheetMusic table.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how do you handle composers
    do you care about the same composer who may create work under different associations (eg they may have some in their own name, some with a partner, some with a group) if so you may need an intersection table between composer and sheet music to associate specific composer(s) with specific sheet music

    do you need to handle arrangers, are arrangers different to composer, is the arrangement significant to your customers

    do you need to have some form of coding and classification system (similar to Amazon and others, if you bought this piece by <blah> have you tried these others pieces by Blah

    I'm surprised there is no publisher, and that you are having to invent a sheetmusic ID, I would have expected any sheet music to have a unique publisher + publication number, do you need an autonumber column (ie all items are in ascendign numeric order), or do you need a specific coding structure for yourself. does that coding structure need some mechanism to hide how many items of sheet music you have, ie you may want to hide how many items of sheet music you have.

    purchase price in orders is suspect in my books, after all its comprised of the cum of the prices in the order

    do you need to cater for any form of discounting mechanism.. or is that the purpose of having a price in the sheetmusic table and a price in the sales table (hate that name... should it be orderdetails?)


    the royalites tabel looks very wierd to me, it looks as if its a composite entity, it has bits which are a header, and it has bits which are detail/transaction level

    I could see where you could have an account for Royalty payments
    eg
    we pay Fred Smith of No 3, Railway Sidings
    AND a transaction table
    We sold n copies of <blah> on dd/mm/yyy, royalties of &#163;x.xx accruing to Fred Smith
    We sold n copies of <blah di blah> on dd/mm/yyy, royalties of &#163;x.xx accruing to Fred Smith
    We sold another n copies of <blah> on dd/mm/yyy, royalties of &#163;x.xx accruing to Fred Smith
    we paid Fred Smith &#163;y.yy on dd/mm/yy
    We sold another n copies of <blah> on dd/mm/yyy, royalties of &#163;x.xx accruing to Fred Smith

    I would expect royalties to be either so much per item (either a percentage or an absolute amount), or so much per composer, which ever route is chose would indicate where the royalty rate would be located

    Id expect some mechanism to cater for who the royalties are paid (eg the copyright owner and an address)

    Id expect the copyright owners to want the opportunity to reconcile sales against royalites recieved, so they can be sure that (ahem) all royalties earned do actaully go to the copyright owner
    Last edited by healdem; 10-22-08 at 05:54.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Oct 2008
    Location
    Cleveland
    Posts
    4
    Trekker -
    I made the diagram in Fireworks as a mock-up so that I can try to understand how the whole thing can work...database stuff is totally new to me so I'm trying to digest a lot.

    Royalties - each composer/client may have his/her own rate but that rate will remain consistent. Brubeck would always be 20%, Katz always 25% etc...

  7. #7
    Join Date
    Oct 2008
    Location
    Cleveland
    Posts
    4
    healdem -
    Great questions! thanks! I am creating this for a publishing company that I have started and am trying desperately to organize www.cellocelli.com

    1. It would be good to have a way to handle a 'Bach/Corelli' composition and have it included in a listing of works by Bach (and in Corelli's list).
    2. Arrangers - wasn't planning on doing much with that info but i should probably include it. As of now, I am the only arranger, and that info is being listed in the descriptions of the item. Technically this should also be linked to royalties in some way. I think arrangers tend to get about 1% of the profit. Yippee!
    3. Classification system - i would love to have this. Right now I have done this all by hand on the various web pages and not connected to any db.
      http://cellocelli.com/SHEET%20MUSIC/...bitterroot.htm
    4. Publisher - as of now I (cellocelli) am the only publisher that is in the online store. I have assigned each item a publication number that includes a rerference to the composer (i.e. Dave Brubeck = DB102, DB107). I would rather use that as the ID rater than the auto generated 1, 2 ,3 etc...YES, I would like to hide the number of items that I have. How do I do it?
    5. As for cataloging, i could add publisher info to my numbering system (CCDB107 or CC-DB107?). Still trying to work out the best methods...
    6. Purchase price - the idea here is that my selling price from the website will be different from the price that I give retail stores and I might offer discounts when merchandise is sold at a live event. The royalty owed would be based on my selling price...trying to figure the best way to track that info...
    7. Royalties - maybe this calculation could be added to an existing table rather than being its own? Royalties would be an amount per item ad therefor are directly connected to the sales record. All of these details are making my brain hurt...


    thanks for the help/guidance!

Posting Permissions

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