Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2006
    Location
    Danbury, CT USA
    Posts
    6

    Thumbs up Which of these 2 table structures looks better?

    Hi everyone, I'm a newbie to dbforums, and a relative newbie to database design, yet I've found myself neck-deep in a rather complicated application (to me, anyway). I'm debating with myself over 2 data structure options for one part of my database, so I figured I'd submit it to the community of experts to see what your reactions are.

    Very brief orientation:
    This database is to track printed music libraries for churches or schools (represented by my table: subscriber).
    Music basically comes in two different ways:
    1. as sheet music for an individual song
    2. as a book/collection of many songs.
    Sometimes a song that is sold as individual sheet music is also included in a collection.
    My application will also sell this music (among other products), so I need a table that has a master list of product ID's for the ordering process.

    So, here are my two options:
    OPTION 1: datastructure01.gif (see attachment)
    This separates songs, collections, and other products into 3 tables. A join table shows which songs belong to collections. The product table provides my master list of products for ordering purposes.

    pros:
    you'll notice a performance table (tracks a subscriber's performance history). data here only relates to songs, never collections or other products. it's nice having this relate discretely to song table. (there are actually 4 other tables in this same situation, I eliminated most here in an attempt to keep this post a little less cluttered)
    cons:
    table structure is clearly a bit complex. also, in product table, two of the foreign key fields will always be null... or, I suppose "0" to keep RI to joined tables.

    OPTION 2: datastructure02.gif (see attachment)
    This uses a single table for all songs, collections, and other products. This table serves nicely for ordering purposes, allowing a single PK to be used for orders. A collections table has two fields (both FK's) linked to the product_id (PK) of my product table. Together they can define what products are collections or belong to collections. (see attachment datastructure03.gif for example data) One problem I don’t know how to solve here, however, is for the situation where a song record can be sold independently AS WELL AS in a collection. The product_upc field in the product table will be null if the song can’t be sold individually… is this a good enough indicator?

    pros:
    table structure is much simpler/cleaner
    cons:
    my product table would have lots of fields that only apply to songs--these fields will be null for all records that are collections or other products.
    I have a feeling that the front-end programming will be a good bit more complicated to ensure that tables such as the performance table can only relate to products that are songs and not collections or other products

    FYI, this will be an OLTP MySQL database with a ColdFusion front-end.

    So, there you have it. What are your opinions? Which method will perform better? Which will be easier to maintain? Thanks for any input!

    --Ken
    Attached Thumbnails Attached Thumbnails datastructure01.gif   datastructure02.gif   datastructure03.gif  

  2. #2
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    What if the product table of the first design were to be changed to have a new column called "product_type"? And not have the song_id, collection_id, etc., columns.

    Additionally, the product_id would have to be the same as either the song_id or collection_id or ... etc. This means that you must make sure that
    (a) the ids in song, collection, etc. are mutually exclusive (possibly generated by the same sequence); and
    (b) you must ensure that for the product_type "Song" for example, the product_id actually exists in the "song" table and not the collection table.

    A simple after insert of each row trigger in song, collection table etc., can ensure this constraint is obeyed. Other alternatives do exist to enforce this constraint.

    The benefit of this is that you do not have null columns, that every time you add a different type of product you do not have to modify the product table.

    You actually may end up writing more code in this case, but would get a slightly cleaner design.

    If only DBMSs could enforce sub-type relationship in a declarative fashion, then you could simply say that song, collection, etc., are (mutually exclusive) types of products, and the DBMS would do the needful.


    By the way, in the first design you must show a mutually exclusive relationship between product and the three "types" of products; and the cardinality should be a 1:1 [1:0,1] actually]

    I am not in favour of bundling everything into a single product entity that could become much larger and unmanageable over time.

    Ravi

  3. #3
    Join Date
    Mar 2006
    Location
    Danbury, CT USA
    Posts
    6
    Quote Originally Posted by rajiravi

    Additionally, the product_id would have to be the same as either the song_id or collection_id or ... etc. This means that you must make sure that
    (a) the ids in song, collection, etc. are mutually exclusive (possibly generated by the same sequence); and
    (b) you must ensure that for the product_type "Song" for example, the product_id actually exists in the "song" table and not the collection table.

    A simple after insert of each row trigger in song, collection table etc., can ensure this constraint is obeyed. Other alternatives do exist to enforce this constraint.
    I like this idea. How would you recommend generating the id's to be mutually exclusive/generated from the same sequence? One way I thought of that might work (I never know for sure on these things, as I'm kind of a newbie) would be to save new records first in the product table. The product table would generate the id; next, take the id from the product table and use that as the song_id, collection_id, etc. That would raise a question, however, as to how to retrieve the new product_id for use in the other tables. If it is sequential, you could run a getmax query on the product table to get the last product_id, but if someone else adds data at the same moment, you could potentially get id's mixed up. Would it work to put a transaction lock on the tables until all the appending has been done?

    Quote Originally Posted by rajiravi
    By the way, in the first design you must show a mutually exclusive relationship between product and the three "types" of products; and the cardinality should be a 1:1 [1:0,1] actually]
    I guess I need to start learning more about MySQL specifically. I've built this thing so far in Access, which doesn't have a way I know of for creation this sort of cardinality. It allows one-to-many or one-to-one relationships, but I don't know of a 1:0,1 relationship. Perhaps this is possible in MySQL.

    Thanks, Ravi for a fine idea to follow up on!

    --Ken

  4. #4
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249

    Type and Sub-Types

    Actually, these are just some common ways of implementing types and sub-types. Unfortunately, DBMSs do not provide much help in creating sub-types declaratively.

    How nice it would be if we could have some declarative syntax of the form:

    PHP Code:
    create table song of type product exclusive ... 
    Regarding the issue of creating unique ids, most DBMS have the concept of a sequence. You create a sequence object and assign values to your identity field (primary key) by asking for the next available number from the sequence.

    The idea of creating a product row first and then the song or collection row is fine. But remember that you still must ensure that if you have created a row in product table that has a product_id of 1 and type "Song" , for example, you must ensure that there exists a row in the song table with id 1.

    In other words, the creation of a row in product table and a row in song table (or collection or ...) must be seen as a single operation. Either both succeed or both fail. Not an easy task.

    Alternatively, as suggested earlier, you could first create the row in song table, and have a trigger on the table such that after inserting a row, it will populate the product table with the appropriate information.

    Both methods will work. There may be other ways of doing the same thing. Just do whatever you think is reasonable, with the understanding that data integrity must not be compromised.


    You said,
    but I don't know of a 1:0,1 relationship
    That is simply my clumsy, informal way of specifying a (non-mandatory) one to zero or one relationship.

Posting Permissions

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