Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2009
    Posts
    2

    creating a set of rows to add en masse (default? template? group?)

    Hello -

    I am learning SQL but I am having trouble setting up what the tables should be for this area of a bigger database. Not sure if this is a SQL or Design question. And I have similar needs in several instances. Not sure if this is the best way to explain, but here goes:

    Events get booked into venues; that's a booking (I have this part ok)
    There are about 20 different salestypes (eg, groups, single, subscription etc).

    Example: each day we record 1 or more sales for each booking
    - Booking1 will have sales recorded for 3 salestypes (Single, Groups and Subscription);
    - Booking2 will have sales recorded for 4 salestypes (Internet, TKTS, CouponX, Groups)

    ie, Booking1 always uses the same subset of SalesTypes (the default set of SalesTypes for Booking1)

    I think my tables are:
    Bookings: BookingID, bookingName
    SalesDays: SalesDayID, Date, SD_BookingID, Note
    SalesDetail: SalesDetailID, salesDayID, salestypeId,valuemoney,valuetix
    SalesTypes: SalesTypeID, salestype_name

    each booking has multiple salesdays
    each salesday has multiple bookings (do I need another table for dates?)
    each salesday has multiples sales, of varying types

    How and/or where do I tag, or identify the applicable subset of default salestypes?
    Do I need another table for the default salestypes for a particular booking?

    Default_Table: Default_id, BookingID, SalesTypeID

    And how do I get all 3 default rows from the default table into the salesdetail table in one fell swoop? Something like (I realize this is not real)

    Insert into SALESDETAIL all from DEFAULT_TABLE where BOOKINGID = 3

    Suggestions?
    Advice?

    Thank you -
    Marion

    (later on, I will want to pivot/transpose this. So that the Days are down the left, and the salestypes are the column headers. But that's for another day.. I just need to get the data in right)


    OR:::: is this too similar to the hateful EAV design that I've read about, and I should make a column for each sales type, adding additional columns when/if necessary? And each row will simply have a lot of empty columns?
    Last edited by mtpaper; 11-04-09 at 19:32. Reason: EAV

  2. #2
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    The modelling has not progressed far enough for me to provide specific responses. You need to work on it some more, and submit it again. Some responses to your specific questions may help ...
    Quote Originally Posted by mtpaper View Post
    Booking1 always uses the same subset of SalesTypes (the default set of SalesTypes for Booking1); How and/or where do I tag, or identify the applicable subset of default salestypes? Do I need another table for the default salestypes for a particular booking?
    Yes. I wouldn't call them "default" SalesType, maybe BookingSalesType.
    each booking has multiple salesdays; each salesday has multiple bookings
    There is no associative table, which you will need to resolve that.
    do I need another table for dates?
    No. On the basis of info provided thus far, it looks like SaleDay will do that just fine.
    And how do I get all 3 default rows from the default table into the salesdetail table in one fell swoop? Something like (I realize this is not real) Insert into SALESDETAIL all from DEFAULT_TABLE where BOOKINGID = 3
    INSERT SalesDetail <column_list>
    ____SELECT <column_or_value_list>
    ________FROM BookingSalesType

    is this too similar to the hateful EAV design that I've read about,
    No. It is a simple database, EAV is not necessary. BTW nothing "hateful" or sinister about it (it is just technology, science); some people get emotional about what they what they fear; and they fear what they do not understand.
    (later on, I will want to pivot/transpose this. So that the Days are down the left, and the salestypes are the column headers. But that's for another day. I just need to get the data in right)
    Yes and no. Sure, get the data modelled correctly. But do not lose sight of the requirements, which should direct and constrain the modelling effort. Eg. Pivoting the columns to rows (or rows to columns) is dead easy (single SELECT, no temp tables) with an EAV or 6NF structure. And cumbersome for a BCNF or 4NF structure. And a Miserable job if you have to use a "denormalised" structure. But if you find EAV/6NF "hateful" ... You can implement BCNF for now, and change the structure (just the tables/columns required for pivoting), and the code, once you have familiarised yourself with it; or Pivot the hard way (temp tables, views on top of the BCNF structure). Or avoid all that and get familiar with EAV/6NF now.

    Further:
    1 Check this post, item [1]. Remove the prefixing of table names in the column names; retain the full name for Primary Keys only.
    2 Use the singular form for naming.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

Posting Permissions

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