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?

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 ...
Yes. I wouldn't call them "default" SalesType, maybe BookingSalesType.There is no associative table, which you will need to resolve that.
No. On the basis of info provided thus far, it looks like SaleDay will do that just fine.
INSERT SalesDetail <column_list>
____SELECT <column_or_value_list>
________FROM BookingSalesType

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.
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.