Database design question for a unique situation

galgorah

Some of you already know that I work as a tourmanager for a few small to mid level (300-2000 capacity venues) bands. Like most people in the touring industry I also have a job I come back to as a programmer in the healthcare industry, but thats besides the point. As such I deal with a lot of information and excel is my best friend. a while ago I decided that I wanted to create a system to better manage this information and help me make better decisions as a result. Knowledge is power, right? I decided I would create an application in .net 3.5 using c#, wpf, sql server 2008 and the ado.net entity framework. Yes I also playing around with new technology. There are very high priced software packages to do this but they are geared more for arena tours. and are 10's of thousands out of your average bands price range. I decided my focus would be more for the bands I work with and those that want to get to a point where they can make a profit off of touring. It would do this by providing a much clearer image of the business aspect of touring and even offer suggestions when it comes to things like handling merch( reordering, sales estimates, etc).

Anyways part of this system is meant to handle the budget expenses, revenue, etc. Things like bus/vehicle costs, merch sales and inventory, guarentees, etc. For the merch aspect I get an a sales sheet every night from the merch person. I then enter into my spreadsheet each product, the quantity sold, and individual price on its own line. This all then gets summed up at the bottom for the total merch revenue. I also then update my inventory sheet at the same time. I use the daily sales summaries down the road when I'm trying to guage estimates for what I think we're going to sell at a particular show and to determine if I need to reorder items, from the manufacturer, and have them delivered to a venue in advance of our arrival. This takes into a account if a city tends to sell more of a certain type of product or a specific product itself. It also helps alleviate the need to pull stuff out of back stock. Currently I generate charts in excel to help with these decisions. But I'd like to let my app do that for me. Basically each product's total quantity sold is its own transaction and the expense/revenue report is a listing of all transactions on daily, weekly, monthly basis. So I need to it to be as flexible and detailed as I can get.

I considered having a transactions table something like:

transID (long, primary key)
transAMT DOUBLE or currency/MONEY
TransCategory (int, foreign key)
showID (long, foreign key to the shows record that this transaction is attached to)

a transCategory table like:

catID (int, primary key)
Description (varchar)
transType(expense or revenue in its own simple lookup table) int
tranParentCat(self referencing relationship, int, for top level categories = null)

a merch sales table seperate from the product table like:

saleID long, primary key
productID, int, foreign key to the product table
qtySold, int
transID, long, foreign key to the transactions table)

I'd really like any input I can get. I'm also considering including the abilty for barcode scanning in the app.

I think you have made this more complicated that it needs to be. Let me see if I can enumerate a list of things you require in a different way to make it seem more managable.<o:p></o:p>

1. You have the concept of merchandise. Merchandise has the following characteristics:<o:p></o:p>

a. Merchandise is of a specific type (Shirt, Brass Knuckles, CD)<o:p></o:p>

b. Merchandise has properties that are defined by the type (Shirt => {Color, Sleeve type, Material, Thickness})<o:p></o:p>

c. You might want to add some specifics to your merchandise (for instance, which bank members are featured on the shirt, Kirk Hammett’s new shirt is probably going to sell better than Lars Ulrich’s new shirt).<o:p></o:p>

d. Merchandise has any other number of properties: base price, cost, bar code, weight, supplier, etc.<o:p></o:p>

2. You have the concept of a show which has the following properties:<o:p></o:p>

a. City, Venue, Expected Number of People, Actual Number of People, Admission rules (over 18, over 16, etc.), etc.<o:p></o:p>

3. You want to record the number of each merchandise that is sold at each show, and at what price (shirts might be on a 20% off sale or something) <o:p></o:p>

4. You want to record total revenue for each show (the amount of money you have at the end probably won’t add up to the amount you should have because of error, theft, etc.<o:p></o:p>

As you can see, you can have a pretty generic sales (transaction) table and put all the smarts in the merchandise. You can always get all the specific information for the sale from the merchandise information.<o:p></o:p>

Anyways, it sounds like a fun project, hope you enjoy working on it.<o:p></o:p>

<o:p></o:p>

<o:p> </o:p>

lpope187

The way I read it is that he wants to combine General Ledger, Purchasing/Expenses and Sales/Invoicing into a single set of tables. From my experience with ERP systems they don't normally do things that way. There is a closing process that rolls up the expense/sales into a specific GL account which has it's own hierarchy separate purchased part / finished good product hierarchies. That type of setup might be too complicated for what the OP is attempting to build, but that's the approach I would take. I'd have a set of tables to define expenses, a separate set to define sales and then during some type of close process adjust the GL chart of accounts.