Want an id as foreign key from two different tables or inforce it

Question

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

Answer 1

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

---

Mohit K. Gupta --- MCTS, MCITP, MCC2011
My Blog OpsVault My MSDN Blog Twitter @SQLCAN

Answer 2

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

Answer 3

Still an issue?

Thanks!

---

Ed Price (a.k.a User Ed), SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

Answer an interesting question? Create a wiki article about it!