View Full Version : Table layout suggestion needed
04-01-2009, 04:42 AM
I'm a database virgin (okay, I experimented some in college, but that doesn't really count ;) ) and need some schema/layout advice.
I have 400 or so Locations; each day they each will generate a half dozen sales numbers. Each location is also associated with a Territory and a Rep, with associations changing on an irregular basis.
I need to extract info such as which locations have the biggest descrepancies between standing orders and widgets actually recieved/accepted, or which reps had the fewest expired widgets returned for credit over a given period, or a simple how many widgets were sold in each terrritory.
So...how should I organize my tables? A big sales table with date, location, and data columns? A table for each location, with a table full of table names? Should the territory/rep data be in that same table(s), or should I put that in its own table? What haven't I even considered?
04-01-2009, 07:05 AM
One hard part here is "changing on an irregular basis."
If it weren't for that, you'd simply have a locations table with foreign keys to the territories and reps tables.
As it is, I think your orders table has to take on that responsibility.
locid : int, primary key [optionally autonumber]
locname : some kind of text
repid : int, PK, autonum?
repname : text
terrid : int, PK, autonum?
terrname : text
prodid : int, PK, autonum
prodname : text
custid : int, PK, autonum
custname : text
invid : int, PK, autonum?
locid : int, FK to locations
repid : int, FK to reps
terrid : int, FK to territories
custid : int, FK to customers
orderdate : datetime
itemid : int, PK, autonum?
invid : int, FK to invoices
prodid : int, FK to products
qty : int
?price : number ? [if product prices aren't fixed]
invnum : int?
orderid : int, fk to orders
invdate : datetime
returnid : int, PK, autonum? [probably don't need this field]
itemid : int, FK to OrderItems table
qty : int [quantity returned]
That's kind of a "tip of the iceberg" minimalist set of tables, I would think. Obviously many other fields in some tables. I have assumed that you accept returns of partial orders, so you have to track down at the orderitem level. And not sure what you mean by "widgets actually received". You mean "received by customer"? If so, then you could mark that in the Invoices table: when invoiced, when received, etc.
But that set of tables should get you started, at least.
04-01-2009, 06:46 PM
Cool, that's simple enough for me to (mostly) grasp, and it takes care of some stuff I hadn't gotten to yet.
Yeah, the politics/stupidity of contant territory and rep changes is bad. I'm not sure about having that in the order table -- a lot of orders are 'standing orders' for 100-widgets-every-friday or whatever. And a rep gets credit for everything about their locations, from the day they take over, whether they've done anything or not. I could insert a replicated order to flag a rep change.... Or maybe have a table of loc/rep/terr with start and end dates? Which would be more efficient as things grow?
Widgets actually recieved -- we don't pay the kind of wages that attract warehouse people that can count. :p One location I was fixing (in the current collection of excel spreadsheets) had gotten the correct number about 30% of the time -- something I really want to be able to track better.
04-02-2009, 01:09 AM
Yeah, I'd have a separate "StandingOrders" table, probably. If you could run a background process, say once a night at 1:00AM, that would copy all StandingOrders for the new day into the Orders table, this would allow you to be sure that the current rep got the credit.
You'd do that by having fields in the Location table for "currentDefaultRepID" and "currentDefaultTerrID" or something like that.
You'd clearly want a history table that shows when the default rep and default territory for each location was changed, for audit purposes if nothing else. But I don't think you have to worry about that in daily operations if the Location table has those "currentDefault" fields.
Now if the default rep changes for location 17 on Wednesday and there's a standing order on Friday, the new default rep gets the credit automagically.
04-02-2009, 04:10 AM
Yeah, I've got hooks at various points of the existing end-of-day processing that I could use for generating Orders from StandingOrders. And having a separate Order for each execution of a standing order would probably also be good for auditing purposes.
Okay I think it's a plan -- I should be able to do most of the daily stuff with fairly simple queries/inserts to the Orders table, plus minor updates to Locations and StandingOrders.
Then if I morph ReturnItems into OrderAdjustments by adding a reason column (FK to AdjReasons table), I should be able to handle returns, shipping losses, overages in anticpation of weather, etc. in consistent yet extensible way.
Neat. Now I just need to code it. :)