So, here is a VERY simplfied piece of my database that just really has me flummoxed. The question is how to properly design the “SessionDetail” table and make the proper foreign keys.
A “Session” is a collection of “details” (in a certain order) and each detail is either a “Word” or a “Letter” repeated n times. But a “Word” is obviously a collection of “Letters” in a certain order. In the end I need to calculate the number of times each letter appears in a Session, regardless of whether it is in a word or not.
“Codex” and “WordType” are not really important here, they are essentially categories and each letter and word belongs to a codex and words come in various types.
Of course, I’m mostly concerned about having the fields WordID and LetterID in SessionDetail as each entry will be either one or the other. And if you look close, you could categorize each letter as its own word, as a word is made of letters, but could be made of just one letter. But I guess then you would repeat all of the letters as “words” (if you wanted to restrict SessionDetails to being compsed of words only), and that copying seems wrong…?
Unfortunately, all my searching of database polymorphism/normalization., etc. has failed to switch on the AHA-moment lightbulb in my dark head.
Other than “A” and “I”, will individual letters ever stand alone in a session detail? Either way, I would do as you suggest and make each letter a word, and link SessionDetail only to Word. It will make both your letter counting and database management easier. You could always add a flag to Word to mark the single letters that aren’t true words, if that information is needed.
Would have to ask a ton of questions to understand this better
Like why would WordID be a one to many from Word to SessionDetail if A “Session” is a collection of “details” (in a certain order) and each detail is either a “Word” or a “Letter” repeated n times.
That’s not what that diagram tells me
EXCLUSIVE OR type relationships like “its one or the other but not both” should be modelled differently
Often this is modelled as a column rather than two different tables as trying to write queries that sometimes select from one or the other is a true pain in the butt
Normally if you think “Invoice” you might have
Invoice
InvoiceID
(maybe derived columns for total, discounts, etc)
Invoice Header
Invoice ID
Sale Date
Order Date
Pickup/Delivery Date
PurchaserID
InvoiceLineItem
InvoiceID
LineItemID (ordered 1 ... N)
SKU
Qty
UnitScale (per item, per ton etc)
PricePerUnit
LineItemDiscount
I certainly could have relationships pictured with the crow’s foot incorrectly.
The object I am trying to create in my app has to look like this:
A session will be an array of sessionDetails. Each session detail will be an array of “letters”, but they must be grouped in certain ways.
SessionDetail(0)
1 apple 3 (the word apple comes first, and is repeated 3 times) - an array of 5 items
2 p 5 (the letter “p” is second, repeated 5 times) - an array of 1 item (or just the one object, I haven’t got this far yet)
3 e 10 (“e” is third, 10 times) - an array of one item
So, I’m planning to make the session detail an arrray of words/letters and this is where I get rather confused. I’ll need to calculate this and get a result for example of the letter “e” showing up 13 times.
In the invoice example, think of a McDonald’s order, where you could order a “Happy Meal” and a “Hamburger”, where the Happy meal is like my “word” - a collection of items, and the Hamburger is simply a single item.
This is my impasse - a Happy Meal is a collection/array of individual items, they have just been defined as a collection previously. (the cashier can just punch the “Happy Meal” button then punch the “Single Hamburger” button).
And I cannot find a similar example anywhere - where a line item can be a collection of smaller things, but some line items are simply “single simple things”.
In the case of a happy meal its one sku item that adds all the items that make it up
They may still show as separate items on the invoice (I haven’t bought one in a long time)
I know places that do that
You order a garage package and they list studs, sheeting shingles insulation etc
And in some ways this makes sense if you want to know how many packs of shingles you sold as you need to include the ones in garage packages as well as all the others you sold outside of garage packages
And even in these cases where “garage” was the iriginal sku they can tell that these shingles were part of a package and others were not. They often have a “from sku” or other means to know this
However that sounds like you don’t want to count this way
Seems to me you have “elements” and these may be compound or not
But that can be determined by “length” if these really are words
basically it and extra column on the eventual end line items that says “I was added to this because someone added this other things that is a group of items”
Assuming the invoice tables are as mostly like I outlined before
Invoice
InvoiceID
(maybe derived columns for total, discounts, etc)
Invoice Header
Invoice ID
Sale Date
Order Date
Pickup/Delivery Date
PurchaserID
InvoiceLineItem
InvoiceID
LineItemID (ordered 1 ... N)
SKU
Qty
UnitScale (per item, per ton etc)
PricePerUnit
LineItemDiscount
GroupSKU
but on InvoiceLineItem we added “group sku” so we can tell it came from a “garage package”
So when someone orders a “garage” the sales person can enter that SKU for “Garage” and what gets added to the invoice is all the individual items, but each says the “GroupSKU” was “Garage” so you can tell it came from an item that is actually composed of many other things and in varying quantities like the garage which would have lots of studs, sheeting and shingles or a happy meal which usually has 1 burger, 1 small fries and a drink
And this way you dont need separate tables to know that there were “group items” and “singles” as they really are all singles just how they got there is different
Some are parts of bigger items (garages) and some are singles - one package of shingles or a few studs or one small order of fries
And because they are all line items in one table finding all the individual items and counts is fast
Dont care if it was from a group or not ? do a sum on all the quantities
Want to know how many came from packages vs not ? do sums with group bys to segregate individual ones from items in the various packages and since the singles wont have anything in the groupSKU they get all counted together
Oh great! This is incredibly helpful. And you would have another table that would store these collections, right? So in one order youd have the GroupSKU pointing to “Garage” and also to “Shed” for example?
Within my particular order, I do need to keep track of the order each SKU comes in, in both the GroupSKU and the order itself, but I think I can get there.
yeah there’d be a way to set up what a “garage” sku consisted of but the actual invoices wouldn’t have references to that EXCEPT by way of the “groupSKU” to know thats how it got entered
but I’d never use it to go lookup a “garage” or its bits
the reason is that you might change what a “garage” sku consisted of
so you would not want an old version to suddenly be updated by a changed definition (say you added insulation - do all the ones you sold before suddenly have insulation ??? no of course not - all it is is a “hey this was entered by using a grouped item sometime in the past and this is what got added because of it”)
the same is true for prices etc - they should be copies of values not looked up (but thats an accounting issue)
what happened in the past should be unaffected by changes now or in the future so entering a “garage sku” basically enters all that data automatically for the person - its COPIES as if they typed it all in by hand
keeping track of the order things come ins is as you had - some “invoice line number” or something
but when you add a group like a garage … what order does that get added
and in what ever tables you set up to define these “grouped things” you could define that order as well and then the copy to the invoice is in that order
[quote=342654:@Norman Palardy]the reason is that you might change what a group sku consisted of so you would not want an old version to suddenly be updated by a changed definition (say you added insulation)
the same is true for prices etc
what happen in the past should be unaffected by changes now or in the future so entering a “garage sku” basically enters all that data automatically for the person - its COPIES as if they typed it all in by hand
[/quote]
You just jumped ahead and answered my looming question! I’ve been really searching for this stuff.
I had the good fortune to work with some truly amazing database design folks & dba’s along the way who have taught me a lot of things
And our community has some as well
(my PK graphic is different)