Sequential Primary Key generation across multiple tables

[rpeare]

that looks right. I do not use relationships in my databases because it 'locks' you into using the defined relationships rather than letting you free-form things but yes, you're correct in your linking system. though usually it goes the other way where you'd link FROM your table tblTransactions and you'd have an alias for each one of your links to tblCompanies (in other words you'd have 3 copies of your tblCompanies in your relationship diagram) but I think the way you've done it is correct.

[Dal Jeanis]

Ummm.... You need to track whether a SALE was made through a DISTRIBUTOR for your SALES AND ACCOUNTING team.

Cue "Ride of the Valkyries" and the chop-chop-chop of helicopters: "I love the smell of mission creep in the morning."

Since the transaction table is handling several transaction types that aren't sales, I'd strongly suggest taking DistributorID off of tblTransactions and having a second table for that tracking function only. (TblTransDistr, TransDistrID PK, TransactionID FK, DistributorID FK)

If a company can be either a sender or a receiver, it's not a problem to have two potential connections there.

Is SubItemID a link back to tblItems?

What does "Active" in tblTransactionDetail get used for? Do you set that to "No" when you've done a stocktake more recently than the transaction?

[rpeare]

I think I may have erred in my last post.

Is there a reason you're storing distributor, customer and supplier in your purchase order table?

Even if you need to track items from supplier to customer that should be done through the item (lot #'s for received items for instance)

if you don't there's no reason to have all 3 on your PO table

[EdaxFlamma]

@rpeare: I do have three copies of tblCompanies. I just stacked them to reduce clutter. We'll see if it works once I fill in all the rest!
My reasoning for having them all in tblTransaction is that all items in a given transaction would have the same supplier, distributor and customer. I feel like that would be storing a lot of data if it was attributed to every single item instead of through the transaction table.

@Dal Jeanis: I had a feeling that this was coming. Although now I'm insanely distracted by the imagery of a "What's Opera Doc?" and "Apocalypse Now" crossover that your last post provided.

Yes SubItemID will link back to tblItems. I have yet to actually do that but I'm working on it. That whole cyclic item/construct(items 1,2,3) inventory thing that is boggling me. I feel like that's may be an issue for another time though. Plus it's no fun if I get all the answers, right?

"Active" in tblTransactionDetail I had planned to use as a way of voiding invoices/transactions should the need arise - canceled orders, additions/subtractions, etc. That way I can keep the record, know why it was canceled etc. Plus our current database already has a similar mechanic so I figured I'd hijack it and try it out once I get there. Similarly I'd use it in the tblCompany etc. to keep record of previous clients without having them muddling up the day to day stuff.

I see your point about the Distributor information. I will make that table and keep it in my back pocket for when I get to the sales portion.

[Dal Jeanis]

Okay. I think you're pretty clean, but unfortunately I'm going to keep typing...

I'm not sure how the architecture of the StockTake will match up with your reporting and tracking. There's an operational issue there, making sure that the numbers entered for a StockTake are explicitly either *before* or *after* any transactions that might occur on or about the same day.

And, looking at the relationships, I'm not sure about one other thing. Will "Making a construct" be represented by a transaction? Or is a construct record implicitly a consumption record as well?

[EdaxFlamma]

The stock take bit I was planning on solving on the "real world" side by scheduling stock takes for a specific time of day at the end of the month. Inventory and stock takes would be time stamped. In theory it sounds nice but I'll see how it comes out in the wash/code. I can foresee a problem where we have printed outgoing invoices but the product hasn't shipped or been packaged yet. But a report/query of the day's orders with the relevant data could be used to amend the stocktake record pre/post entry. Worst case scenario I can drop the stock take and just keep a running calculation and see how our computers holds up with the data crunching.

I was planning on having the construct record act as a consumption record. Would you recommend otherwise? Laying it out this way made it easier for me to deal with the flow of things.

The thought of having an invoice transaction that nets an item which is then used as a component of a construct which is considered its own item and whose individual item usages are transactions themselves, that will then be used in an outgoing product transaction made my brain feel a bit sick. But if it must be done, I'll surely take a crack at it.

[Dal Jeanis]

I'm far enough away that I'm just speculating based on a very fuzzy model. Wearing my business analyst hat, I said "look over here and see whether there's an issue". You looked and said, "I see that, and I think I can make it work". If you can visualize/work out/talk out/conceptualize how it can work, that's all that you really need.

As you said, it will all come out in the wash (code). If you run into anything you can't figure out, call for help on a new thread, and we'll come running.

[EdaxFlamma]

You guys are great, thank you. I'll let rpear get his last word in if he wants but otherwise I think we may be solved...until next time!

[rpeare]

last word!