[DB Design Advice] How to handle this situation. 4

[DSect]

Hello -

I'm trying to unify many satellite DBs @ my work. Right now, they do not share the same "Customers" table. I'm trying to bring the systems together with a new DB.

Here's the structure of some of these DBs:
Customers
Orders
Order Details
Payment Information
^ All structured like a typical "order entry" system + some ref. tables and so on.

Here's the "problem" I'm running into:

The old DB's Order Details tables had fields that were specific to what they were tracking.
For instance, one DB tracks sales of products and it's order details table would have fields like Order_Id (FK), Product_Id, Quantity, Price, Extended Price.

Another DB tracks subscriptions to publications, it's Order Details table would have fields like Order_Id (FK), Publication_Id, Subscription_Start, Subscription_End, etc..

The Order table for most of these systems are very simple and they have the following fields: Order_Id, Customer_Id, Order_Date, Status, Comments, etc..

In summary:

I have many order entry databases which use separate "customers" tables (old dbs).

They also have Order Detail tables which vary from system to system.

How do I unify these systems, mainly - Can I make a common Customers, Orders and Payments table for these systems and have different "Order Details" tables (with different fields) for the kinds of stuff we sell?

Is it common practice to make different "Order Details" tables for each kind of product / service you sell?

I'm shortsighted right now - Can this be handled another way, by using better table structure or by making changes to the actual Orders tables?

I hope you can understand my question. I basically cannot figure out how to store this stuff in one system because the different products and services that we sell seem to need their own Details tables to handle the specifics. Maybe someone can present a solution to this problem from an angle that I haven't seen?

Thanks!

 

[vongrunt]

How do I unify these systems, mainly - Can I make a common Customers, Orders and Payments table for these systems and have different "Order Details" tables (with different fields) for the kinds of stuff we sell?

Another way may be vertical split - common columns to the left, specific columns to the right, 1:1 relationship between. It all depends on how many common data you have (products vs publications).

 

[donutman]

I have that situation in my business (bakery). There are two detail tables: one for custom cakes and one for everything else (cookies, pies, donuts, etc.) It simply wouldn't make sense to try to merge those tables, but the application must, of course, know which to work with.
In the orders table, there is a column which denotes which type of order the customer has ordered. So there is a single customer table, a single order table and two order detail tables.
Hope this helps define an option.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[DSect]

Thanks guys!

After conferring w/ some other people, the multi-order or multi-order detail table would be the way to go.

One caveat though..

If I did do multiple "orders" tables, one for each of the profit centers, I would have a hard time making a UID across the multiple tables.

I've looked into GUID but I'm kinda new at using a GUID, soo.. The problem I'm having is to be able to assign a REF # or INVOICE # to an order and have it unique across multiple tables.

Before I get too deep into this, I am thinkng about going the "multiple order details table" route. I wouldn't have to worry about the GUID at all. The ROW_ID could be my INVOICE / REF #.

Also - By simply adding an "ORDER TYPE" field to the order, I can facilitate easier things like "Show me all orders for X", etc..

After all - the order table COULD be the same for everything (Customer_ID, Date_Placed, Shipping Info, etc..) and then have the details or details of details be different for each profit center.

I'm going to be doing a lot of thinking for a bit here//

Thanks again all! Very helpful information..

Again - I'm going to try it w/ a common order table and multiple details tables..

 

[donutman]

BTW, the reason I didn't use record locking is that in our business we have users who open records for examination (and possible update) but finally make no change at all. Sometimes they walk away from their workstation and don't close out the examination. By using the re-read before write technique you don't have to lock the rows. The downside of this is that it's possible that two users do in fact want to update the same row. That is an extremely unlikely event in our business.
To be more specific, I don't write a new UpdateDateTime (timestamp) when the record is read (that would be wasteful). I just read the current UpdateDateTime and then re-read it prior to writing a new UpdateDateTime. The SP rejects the update if the UpdateDateTimes don't match.
You may want to avoid giving your Id in the Order table any sort of external meaning (invoice #). Best Practice is to simply use an identity column that has no meaning.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

There's always the option for a vertical layout, but I think I'm getting into some areas that are a little deep. The problem is that you'd either have to use sql_variant or have one column for each datatype with all but one column empty:

OrderDetails
[tt]OrderID(int) DataDesc(int)* DataValue(variant)
1 Product_ID 57
1 Quantity 43
1 Price 224.87
2 Publication_ID 'AE5526'
2 Subs_Start 12/1/2004
2 Subs_End 12/1/2005[/tt]

*DataDesc is an int, lookup into a description table, but I list char values for clarity.

The multiple-detail-tables idea is a good one, though. But I would recommend, instead of having one for each different DB, combine as many as you can, so you end up with your "basic five kinds" or whatever.

Then, you can create some views that merge these together when you need information from them in sensible ways.

For example, if one details type has OrderDate but another has SubscriptionStart, your view could look something like this:

SELECT
   ServiceStart = CASE
       WHEN Detail1.OrderID IS NOT NULL THEN OrderDate
       WHEN Detail2.OrderID IS NOT NULL THEN SubscriptionStart
       ELSE Null
   END
FROM
   OrdersTable O
   LEFT JOIN Detail1 ON O.OrderID = Detail1.OrderID
   LEFT JOIN Detail2 ON O.OrderID = Detail2.OrderID

Another method could be:

SELECT
      OrderID,
      ServiceStart = OrderDate,
      OtherColumns
   FROM
      OrdersTable O
      INNER JOIN Detail1 ON O.OrderID = Detail2.OrderID
UNION SELECT
      OrderID,
      SubscriptionStart,
      OtherColumns
   FROM
      OrdersTable O
      INNER JOIN Detail2 ON O.OrderID = Detail2.OrderID

Then,

SELECT * FROM MyJoinedView WHERE ServiceStart >= '5/1/2004'

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[DSect]

More good advice - ESquared..

Here's a situation anagalous to mine. If you all could give me a pointer using this example, that would be great:

A "Car Repair" place does a few things: Repairs cars and sells parts.

Of course, the repair place would have a "customers" table, but how would the order table(s) look to be able to handle both repairs and parts sales?

If I had to answer that, I'd be kinda stuck. I'd be stuck because I'd know that while "repairs" and "parts sales" could / would have the same customers, the fields required to track repair are much different than selling parts.

In your opinion, how would the "Car Repair" place store "orders"?


Here's how I would answer it right now - to the best of my knowledge:
A table for all orders (tblOrders) an intermediary table w/ line items (tblOrderLineItems), each line item could have foreign to "details" tables and a "type" code (r = repair, p = parts). Line items FK would be populated by row_ids of details tables specific to the operations (tblRepairDetails) & (tblPartSalesDetails)?

So the car repair place would have:
tblCustomers (customers)
tblOrders (order info: cust_id, date, amount_due, status, etc..)
tblOrderLineItems (order_id, order_type)
tblRepairDetails (line_item_id, job_code, labor_hours, mechanic_code)
tblPartSalesDetails (line_item_id, prod_code, quantity, price, ext_price)

^^ What do you think of that for handling a car repair place?

Again - I'm using that because the imaginary car place wants a syngeristic system w/ one customer repository, one order repository and be able to track repairs and parts sales. The system would have a billing / invoicing system that ties back to orders, so that's why they desire one orders table.

Thanks again!!!!!!

 

[ESquared]

Well, the first thought that comes to mind is to get away from thinking of the things in terms of repairs and parts.

Is there some way to think of them so that they have the same or similar attributes?

[tt]Repairs Parts
Mechanic Supplier
Hours Quantity
Hrly Rate Price Each
Wkend Rate ExtPrice
JobCode PartCode[/tt]

I can't think of a word at the moment which covers both of these, but I see no reason that you can't combine them into one table, with a flag for what kind of item each one is.

And, just like parts, labor can have sub-pieces. For example, a doohickey replacement might consist of 1 doohickey housing, 2 doohicky gaskets, and 1 flow interruptor. A car inspection might consist of 1 check washer fluid, 1 test compression, and so on.

You can have packages of parts and packages of labor. You can set a price for individual parts/labor or only set a price on the package.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[DSect]

Yeah E.. The problem is that we cover a lot of areas so I thought the car repair would be a good secenario.

It's hard for me to find similarities between these:
T-Shirts, Mic products sold as pieces
Classified Ad Sales (and management)
Meeting / Event Registration
Publication Subscriptions
Organization Membership Management


That's 1/2 of what we deal with.. These are all in different DBs now (it's true!).

Not only are the DBs spread out, they are hurting. I;ve been bringing / redoing a couple and thats where I started to think about pulling it into a common system since they all would share the same customers.

A good example would be publication subscriptions. I have the subscriptions broken down into PLU (price lookups) but a subscription has a start and end date.. Nothing else will use that.

Classified ads. They have many particulars. The end result can be broken down into PLU type things but there are things like word count + 7 other ad specific fields..

I'm a little confused right now as to what to do.. Time to sleep on it and see what tomorrow brings!

THanks again! Any insight is appreciated..

 

[SQLSister]

I would go about this in terms of what you will want out of the systems as far as reports and view screens.

Try if at all possible to design such that all the data needed for unified reports on sales is available in one set of tables so that you avoid union queries. Then put the details you want for specific orders which may appear on the order form printout and any screens specific to the order but which probably are not in agregated reports in separate tables since the details will be different.

For instance, you probably want to see total sales by month, maybe total sales by customer, maybe totals sales by customer and category (ad, clothing, etc.) and total sales by category as aggregrate reports. However in these reports you don't care what size the t-shirt was.

oO you want:
Customer table
Order table which includes order date, customer ID, order total, payment method
MAybe a shipping table (different parts of an order might ship on different dates, so it will be a one to many relationship to the order table.)
Then Multiple order details tables
Main order details with the items that are similar for all systems (Order no,ItemNo, Description, price, category)
And then related tables for the details specific to each category like size, color, ad run dates, etc. (these havea pone to one relationship to the order details table)

Now you can easily query one group of tables for aggregate reports and other tables for the specifics on the orders.

To avoid the use of dynamic SQL in creating the order form and screens showing the order, you could add a notes field to the main order table which takes the key elements by category and puts them in it through a trigger. So now you can see what you need for an order without having to dynamically figure out which table, but the details are still stored in the appropriate table so that people working in that area can use for analysis purposes (so they can figure out easily that 35% of the t-shirts ordered are size XL for instance, or that classes scheduled in June tend to fill up better than classes schedueld in Jan.)

Yep, this denormalizes slightly, but I think it would be worth it iterms of performance and security and ability to easily analyze the data.


Questions about posting. See faq183-874

 

[donutman]

As usual SQLSister does a great job. Sis, what's your opinion on how to

MAybe a shipping table (different parts of an order might ship on different dates, so it will be a one to many relationship to the order table.)

Would the various detail rows include a FK reference to the Shipping table? Would a single order then have a row for each shipping date or would each shipping date force the creation of a row in the Order table? It's not entirely clear what you were contemplating.
How would you handle subsequent edits of an order that might cancel one item of several because it wasn't going to be shipped at the same time as the rest of the order? Would you use a trigger to delete the shipping row (provided no other detail row refereed to it) or just deactivate it. You could just leave it even though there is no active row in the detail table that references that shipping row. Or you could have a scheduled job that periodically deletes rows in the shipping table that aren't referenced by the detail tables. What are your thoughts on that?
This is somewhat of a philosophical question about the degree of logical purity (for the lack of a better term) that applications and databases should have in the real world. Another words do you knowingly leave some untidiness in databases for which you have had full design responsibility? BTW, I don't consider the issue of de-normalized data vs full normalization as untidiness, those issues are a judgment call about what's best for the situation.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

I would probably set up a shipping ID field in the order detail table. Then when I shipped one or more parts, I would create the ship record and add the shippingID to the details of the parts that were shipped. What I would not set up would be a Primary key foreign key relationship, especially not one with cascade delete. So suppose you had an order with 4 items, 2 ship on one date, one ships on another date and one is a training class and thus doesn't ship at all.

Then you would have one record in the order table, 2 records in the shipping table, 4 records in the main order details table which included shipIDs for three of them. YOu would also have 4 records spread amoung the various specific order detail tables.

If I decide to delete a part because it won't ship for amonth, then I would delete it from the main order detail table and specific detail table. I dounbt I woudl keep it and make it inactive as there is little likelihood I would need it again. If the customer changes his mind, it would be added in as a new order.

If you choose to have proposed ship dates and actual ship dates, then you could store both in the ship table, but you would then have to check this table to make sure that each shipid still has records related to it when you delete a part. Or you could store the proposed dates in the main order table and not create a ship record until something actually ships. I'd probably go with the latter.

One thing I would do before finalizing my design is to flow chart all this out so I know exactly what actions will have to affect what other tables.




Questions about posting. See faq183-874

 

[donutman]

Or you could store the proposed dates in the main order table and not create a ship record until something actually ships.

If you used that approach how would you know which parts went with each delivery when there are multiple ship dates?
But the question I was really curious about was the untidiness of leaving a row in the Shipping table if all rows of the Detail table (related to that shipment) were deleted. You can't do a cascade delete because there are several Detail tables, besides cascades work in the opposite direction (?) ie. from parent to child. Would it concern you if there were parent records in the Shipping table with no child records in the Detail tables?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

Yes, it would. Clearly you would not want to delete the ship record if you actually did ship parts. But if the order got cancelled before shipment and you used a proposed ship date, then yes indeed you would want to have a trigger on the order details table to check to see if there were any other parts which still had this ShipID and if not, then delete the record from the ship table.

In answer to the question about how would you know which parts went with which shipment, that is why you have a shipid in the order details table. if you have proposed ship dates (to make up potential shipments and give the custostomer an idea as when things will ship) you will need some triggers to check what parts are actually shipped against the ones intended to be in the shipment at the time of order. I would have the user interface set up so that I couldn't exclude a part from the shipment unless I gave it a new potential ship date. This would in turn also create a new shipment or add it to another existing shipment from the same order. In fact now that I think about it you might want the ship table to relate to customer id and have a related table for shipID and orderid, so that if a customer orders frequently (say me and amazon.com for instance!), the shipments could be combined for items not sent out immediately. All depends on the nature of your business though what specifically is the best thing to do. THat's why the flow charts!

Questions about posting. See faq183-874

 

[DSect]

I see that this has provoked a little though

Thanks for the info, all!

I'll post an ERD soon (hopefully!) and maybe you all can see how I was proposing handling my situation..

Again - Thanks! Very valuable informaiton here so far..

My situation is probably common for anyone who is doing "tech housecleaning".