ARGH! Multiple Many-to-Many Relationships!

[Pienuts]

Great, I'll chew on that for a bit and I'll post the sample skeleton tomorrow!

[orange]

Don't get too detailed before you do some test data. I'm not all that comfortable with ServiceUsed.
I think we have to work with ServiceList and figure out how to add Employees and Horses to match the Customer's Order/Booking.


Update:
I've played with this a bit and included a Booking set up and revised the ServiceUsed. You still know your "ranch business" best, so you can revise/delete/ignore as appropriate to your needs.

BookingStatus has things like below (but you could add more as you see fit)
-Recorded
-Cancelled
-Confirmed with Deposit
-Completed and Paid

The way I foresaw your Invoice is, once a Booking is Confirmed you could create/start an Invoice (in progress).
The Invoice line items would be for Horse services and/or Employee services (ServiceType E or H) since either
EmpOnServId or HorseOnServId would be blank/empty accordingly. All services would be rolled up for this Invoice / Billing. This would require you to have a list of Employee services and Horse Services.
I think the Injury and HorseInjury are covered now??. Again, a little review by you may shoot a lot of this apart.

I don't know where Selling a Horse fits in all this.

Anyway, that's what I have at the moment. Hope it's useful to you

[Pienuts]

Orange,
This looks SO useful! Unfortunately, my computer crashed yesterday and is in the shop. Hopefully I'll get it soon, with everything recovered! So give me a few days, I'll finish up the skeleton and we can discuss some more!

It looks like the horse injury situation is great; and it looks like there is ability for none, one or many horses and employees for each service - awesome! I like the booking tables too! When selling a horse, it will just involve a report showing all of the horse's injuries and maintenance. Then I will change the horse's status to sold (I'll want to keep the records even when they're gone).

One thing that isn't yet incorporated is that the amount of riders involved in a service often determines the cost and it's not directly related to horses used (customers can bring their own horses). I usually have three hourly rates - for a single rider, for two riders, and for three or more. I may have messed that up when I created the CostPerHourPerRider field... I will have to consider further.

Thanks, Orange! I hope I'm back and running soon, so I can try out your suggestions!

[orange]

Too bad about your computer -- been there...

Yes, there are transactions that may not be accommodated because of my not being familiar with the subject matter.
In the version of Erwin I have (free community edition) it does not have the ability to generate SQL to create the actual database. But I have tried to create the tables and relationships in Access 2010.

If I get some time, I may try to add records, but it won't be a user interface. Just a proof of concept to allow queries for things like
- available horses are those not currently booked or injured.
- maintenance for any horse
- cancelling a booking ( and possibly releasing any resources)

The rates seem to be related to "resource usage (car rental days)" related. It will start to comme out as you do some testing.

Do you have an "employee leads a trail ride" or do you have a "take these 3 horses and be back in 4 hours"?
Picnic at the ranch days, hay rides... I was dreaming up some "services".

What is the ranch involvement when someone brings their own horse???

Any way, don't get too excited until you get some test transactions and run them against the model.