Complex Data Relationship

[markchimes]

Hi All,

I would like to describe a complex data relation that is required in a project, hoping someone can assist with the table schema.

I have been asked to write an application to handle the hiring of formal clothes for weddings, funerals, deb balls , etc. The hiring party may have anywhere from 1 to 25 people who need to be fitted.
Each person may have the same or different garments, but each will certainly have different size (and maybe color) requirements.
For example Fred comes in and hires a black Camry Jacket 48 sleeve, a Maroon Flair Waistcoat 42 Chest, Black London Trousers 42R, a standard White shirt 42 Chest 32 Neck and Black Cowboy boots Size 9.
So far, this is easy stuff.

However, when Fred books these garments for a wedding they are reserved for 2 days before and 2 days after the event. If for a Deb Ball, it is 1 day before and 1 day after. So the time the garments are reserved depends on the type of function Fred is attending.

Now, here is my problem.
When Fred comes in to select the jacket, trousers, etc that he likes, the attendant needs to know that the Camry Jacket 48 is available, but the Flair Waistcoat 42 is not. Take into account that, for some sizes, the are multiple garments. (The store may have 3x Camry Jackets 48 to rent, but only 2x Camry Jacket 46 for example).

Does anyone have any thoughts on how to structure the SQL tables so I am able to lookup garment availability based on Type/style/Color/Size/NumberToRent/FunctionPeriod without duplicating data in multiple tables?

Looking forward to hearing from you all :-)

Cheers,
Mark Chimes

[Shashi Sadasivan]

This should have been posted in one of the sql forums, anyhows it will be shifted by someone hopefully.

so I came up with 5 types of tables[/size][/font]
1. Inventory (Name, Description, InventoryID(PK))
2. Type (InventoryID(FK), size, Type, colour, Stock, StockRented
3. Functions (FunctionID(PK), Name, PreRentDays, PostRentDays)
4. Customers (CustomerID(PK), Name,etc....)
5 Transactions (TransactionID(PK), TypeID(FK), CustomerID(FK), DateRented, DueDate, DateReturned, TotalCost, PaidCost)

does this make much sense?

cheers, happy coding!

[markchimes]

[quote=Shashi Sadasivan]This should have been posted in one of the sql forums, anyhows it will be shifted by someone hopefully.

Sorry, I'm new to this forum.

So I came up with 5 types of tables[/size][/font]
1. Inventory (Name, Description, InventoryID(PK))
2. Type (InventoryID(FK), size, Type, colour, Stock, StockRented
3. Functions (FunctionID(PK), Name, PreRentDays, PostRentDays)
4. Customers (CustomerID(PK), Name,etc....)
5 Transactions (TransactionID(PK), TypeID(FK), CustomerID(FK), DateRented, DueDate, DateReturned, TotalCost, PaidCost)
does this make much sense?

Hmmm, I am still a little confused.
How do I know that a garment is available or not when booking out an existing garment?
The Type.StockRented column will not work as the one garment is booked multiple times in any month.
What is the Type.Stock column use for?

cheers,
Mark



cheers, happy coding!

[Shashi Sadasivan]

Hi Mark,

well..after a few shots around this site you will get your way around

Ok..the Type.Stock contains the number of items for that type which the shop owns (regardless if it is rented or not)

stockrented = the stock of that type which is currently out of the shop (being worn by someone else :P)

I assumed that there was no booking involved.
But however to the Transaction Table add a field called DateToRent

So now if a customer comes in, and want to book black pants32 size...qty = 2, for a certain date then you will have to go thru the transaction table and and for each of the rows which contain that typeID find those rows where date of booking falls inbetwee dateToRent and dueDate.
find the sum of the qty of those rows, compare it to Type.Stock, and you would know how many are availabe!

making any sense now?

cheers