Friday, July 20, 2012 3:27 PM
First of all I am pretty novice but aspiring person in Programming. I am creating a database which is for billing System in Photo Studio. My problems are as under:
I am USING C# .NET and WPF
(I wont mind programmed solution if these cant be done in DB alone)
1. For each orderInfo.OrderID I have (customerID from customer table which also contain customer name) but this customerID is optional for permanent customers only. Thus if customer is not permanent I dont need to assign customerID to orderID, but still I want a name.
IF orderInfo row got CustomerID.. I want to use customer name from customerInfo table
else I want to create name in orderInfo only (Any foolproof way to do this?)
2. Each order may have multiple tasks eg 2 photos of size 4x6 and 3 of size 6x8 and One Video so I created table taskInfo for that with primary key taskID (which I suspect will amount to hugeee data as each order can contain multiple tasks and lots or orders pouring in daily!!!)
I tried like this as per recommendations in forums
Create Task ID and Task Type in TaskInfo
Create PhotoInfo Table with PhotoID being primary key taskID refering to taskid in taskInfo
Similarly for VideoInfo table VideoID being primary key and taskID refering to taskId in taskInfo
How can I ensure that the same taskID is not being used in both PhotoID and VideoID
Please tell exact way to define table in VS2010 SQL as I am using GUI to create tables instead of CMD Line
Any Other optimisation recommendation please??
BTW I have considered creaing TASKID and TASKTYPE as combined Primary key and using this as combined foreign key in PhotoINfo and VideoInfo wich check constraint that TASKTYPE=Photo in photoInfo and equal to Video in VideoInfo but that doesent leave taskID as Unique which is also needed when selecting from combobox using ID (else task of user will increase ie select type then select ID)
I posed it at http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/b1851712-0a7b-479d-bac2-8827cf135893 then someone said i shall post in this forum
Friday, July 20, 2012 11:22 PM
This is how I would design this database ...
Customer (CustomerID, CustomerName, CustomerType, etc)
Stores all customers permanent or temporary, makes it easy to make a reference to OrderInfo table. Instead have a customer type field to identify the customers that are not permanent, so you can clean up their data later (customer data, order data, etc.).
Task (TaskID, TaskInfo, TaskCost, etc)
List of services your studio offers.
OrderInfo (OrderId, CustomerID, etc.)
Order for the Cx in question, will contain information like drop of time, pickup time, etc.
PhotoInfo (PhotoID, TaskID, OrderID, MediaID, Qty, Description)
List of all the photos customers ordered for a given order.
VideoInfo (VideoID, TaskID, OrderID, MediaID, Qty, Description)
List of all the videos customers ordered for a given order.
Media (MediaID, MediaInfo, MediaCost)
Type of media the customer requested, high gloss paper, dvd, cd, etc.
Just an idea... I am sure there are many options for this scenario :).
Saturday, July 21, 2012 6:36 AM
Thanks for your valuable reply but my problem is a little different let me try again:
Each order may contain multiple tasks and tasks is generalization of photo video etc... I dont need taskID on customer end.. its just used like this
taskInfo table contain taskid:primary key
then taskDescription:which is primary key from photoinfo or videoInfo so i can fetch data like cost of this specific photo size etc into task when I join tables into single view getting it??
then quantity: which when multiplied by cost from photoinfo will give task cost
all taskcosts will add up to create order cost
also this table will contain ORDERID which is primary key from OrderInfo so i can get all tasks in any specific order
My problem is to implement the underlined and italic text part of above problem
also if possible tell me if I shall create a view for complete Order Description how the orderCost can be saved in DB which is actually derived!!! or shall i generate it at runtime always!! which my be tedious if i wana know balance of any customerID which can have multiple orders and multiple tasks in each!!
Wednesday, January 16, 2013 2:35 AMOwner
Still an issue?
- Marked As Answer by Ed Price - MSFTMicrosoft Employee, Owner Monday, February 18, 2013 6:52 AM