CODEWITHSUNDEEP
databasedatabase-designdata-modeling
Mohamad
Mohamad

Reputation: 35359

Using inheritance to solve a design problem

I need a table that stores items and tracks when an item is...

  1. queued
  2. marked for shipping
  3. shipped
  4. marked for return
  5. returned

The table also needs to tell me how many items a customer...

  1. has received in a month
  2. has at home at this time

I tried using inheritance. I assigned each step a typeId (from 1 to 5, each id representing the current step in the workflow). This approach is not ideal because updating a workflow step erases history.

For example, if an item moves from shipped (typeId 3) to returned (typeId 5) we lose an accurate count for shipped items.

How should I approach this? Also, I prefer to keep the data in one table unless I get a compelling reason not to.

Update

Items are mailed to customers incrementally. There are limits to how many items a customer can receive within a month period, and limits on how many items a customer can have at home at any given time. For this example, let's assume 4 for earlier and 2 for the latter. A customers queue can have any number of items. For this reason, items in the queue need to be ranked so the order of items sent can be according to the customers preference.

Items that have shipped already will need to fall out of ranking (the customer can no longer modify rank after an item is sent).

Upvotes: 2

Views: 102

Answers (2)

Damir Sudarevic
Damir Sudarevic

Reputation: 22177

No inheritance here. Time fields are actually date-time. A row is entered into the Tracking table when a customer adds an item to the queue. Other time columns from TimeMarkedShip to TimeReturned are NULL until the action happens. The TimeQueued is part of the primary key in order to allow a customer to rent an item more than once (sounds like video rentals to me).

enter image description here

To count items that a customer has at home you could use something like

select count(1)
from Tracking
where CustomerID = 1755
  and TimeShipped  is not NULL
  and TimeReturned is NULL ;

Upvotes: 3

Chris Pousset
Chris Pousset

Reputation: 346

Rather than a typeID for the current step, it looks like you need a boolean column for each step. Then when you want to count the "net shipped" items, you can subtract the "returned" items from the "shipped" items.

In addition, if you want to track the history, you could make each of these steps a nullable date field, so that you can see that an item was shipped on 3/5/11 and returned on 4/1/11. This may help if you're using this table in other ways, such as managing your shipping/receiving or billing. A NULL, of course, would indicate that the step has not been performed (yet).

Upvotes: 1

Related Questions