How to design a table hierarchy for orders with different products parameters

Question

I've studied the 5 ways that were mentioned here

I am still having trouble finding one to implement into a new DB design I'm creating. Each one of the mentioned ways has it's pros and cons and the cons were not suitable for my needs.

I will explain in short what I need. My company has divisions and each division has categories (I am just listing a few for an example):

1. Video editing (VE) cat 1: Events cat 2: Clips cat 3: Production

2. Graphic Design (GD) cat 1: Digital albums cat 2: Photo manipulation cat 3: Photo retouching

As you can see, each product has completely different attributes.

As of now, I have an Excel file for each division, where each file has tabs for all the clients (which is why I'm making a DB).

On my website now, each category has an ORDER FORM that is designed for the attributes in that category. I'm having trouble implementing this into an ORDERS table hierarchy.

As you can see in my screenshot, (don't mind the naming... I used "attributes" so you can see what it is. I will be changing this to "att" for short. I also just made some basic columns for testing) the four right tables are what the ORDER FORMS today look like when clients order products from these categories. So if you think about it, they should be a ONE-TO-ONE relationship with the ORDER_DETAIL table (like I did to one of them in the screenshot) because you can only have ONE row of these in an order. But an issue I was thinking is that the name "order_detailsID" doesn't match and I've learned that relation keys should have the same names... and I can't name the attribute table's PK's "order_detailsID" because that's not what it is... it's attributes.

Maybe my ORDERS and ORDERS_DETAILS are wrong... I don't know. I need help to figure out how to make clients be able to order a product and based on that, to get the appropriate attribute table to fill out a row.

(and of course, the FK "order_detailsID" field in the att tables are not being used as I didn't know if I will need them.)

What would you suggest?

Click HERE for large original quality of screenshot.

Answer 1

So in four tables

Video_clips_att
Video_events_attributes
GD_album_attributes
GD_manipulation_att

you can have foreign key on Order_details OR on Orders.

This depends if you want to connect each attribute to a specific product through Order_details or just to a specific order in Orders.

A query like

SELECT Video_clips_att.* FROM Video_clips_att INNER JOIN Order_details ON Video_clips_att.order_detailsID = Order_details.order_detailsID INNER JOIN Orders ON Order_details.orderID = Orders.orderID WHERE orderID = N'…';
SELECT Video_events_attributes.* FROM Video_events_attributes INNER JOIN Order_details ON Video_events_attributes.order_detailsID = Order_details.order_detailsID INNER JOIN Orders ON Order_details.orderID = Orders.orderID WHERE orderID = N'…';
SELECT GD_album_attributes.* FROM GD_album_attributes INNER JOIN Order_details ON GD_album_attributes.order_detailsID = Order_details.order_detailsID INNER JOIN Orders ON Order_details.orderID = Orders.orderID WHERE orderID = N'…';
SELECT GD_manipulation_att.* FROM GD_manipulation_att INNER JOIN Order_details ON GD_manipulation_att.order_detailsID = Order_details.order_detailsID INNER JOIN Orders ON Order_details.orderID = Orders.orderID WHERE orderID = N'…';

Will return you the four collections, one of each of these data sets, belonging to a particular order.

Now, when you are using this data in your application, you can do like this:

public class IOrder {
    public ICollection<IOrderAttributes> OrderAttributes;
}

public class Order : IOrder {
    public ICollection<VideoClipAttributes> GetAllVideoClipAttributes {
        return OrderAttributes.Where(x => typeof(x) == typeof(VideoClipAttributes)).Select(x => (VideoClipAttributes)x);
    }
}

public class OrderAttributes : IOrderAttributes {
    //work and stuff
}

public class VideoClipAttributes : OrderAttributes
{
    //more work and stuff
}

well, you get the idea.

It will not be too much work to grab data from all these four tables and put it into four different types which inherit from the same type and then put them all in one nice collection which is reasonably detached and abstracted. And if you want to be able to identify each attributes object by its respective product, you should use the order_detailsID foreign key on your attributes tables.

This will work because the order_details table does not have to have a foreign key back to the attributes table, so you don’t need an artificial layer overtop to maintain that relationship.

EDIT 2016-10-24:

Since you want a one order to one attributes relationship, a sensible solution would be to merge your four tables into one:

Order_attributes{
    attributesID
    orderID
    video_clips_typeID
    employeeID
    video_clip_statusID
    title
    date
    languageID
    song_choiceID
    video_event_typeID
    video_event_statusID
    unique_name
    bride_name
    groom_name
    other_name
    album_typeID
    GD_album_statusID
    need_names
    spreads
    size_w_mm
    size_h_mm
    has_cover
    cover_size_w_mm
    cover_size_h_mm
    raw_image_amount
    GD_manipulation_detailID
    GD_manipulation_statusID
    attributes_type
}

Then, your order can have a member like so:

public class IOrder {
    IOrderAttributes attributes
}

To interface with your database, you can have a class like OrderAttributesStore, which replicates the schema of the order attributes table exactly, but when you construct the individual instance of your Order, you can do a switch like that:

switch (OrderAttributesStore.AttributesType){
    case 0:
        return new VideoClipsAttributes(){property=value,...};
    Case 1:
        ...
 }

That's how this thing works: https://dotnetfiddle.net/Glyhj2

You can go further and streamline the process further, but that's the general idea.

Then you can add a foreign key in Orders for Order_attributes and make it unique.