Non-essential retail to open today - by appointment in many stores... Galleries and museums to reopen. Inter-county travel to recommence. Public libraries to partially reopen - mostly for click and collect... 50 to be allowed at funerals and weddings...
@ypercubeᵀᴹ (and others interested in the debate over enforcing [inter- | intra-] [record | table] level constraints), please see the question below. I took a "multi-record" joining table approach which does answer the question but this approach brings up all sorts of interesting Referential Integrity (and/or CHECK CONSTRAINT) issues. I've discussed some of these issues in the answer/fiddle.
I would be grateful for any errors in my logic/train of thought and how my schema could be improved to enforce the desired integrity rules - I would also be interested in hearing about any completely different potential approaches that may answer some or all of this issues raised. Equally, if there are any glaring lacunae, I would be delighted to have those pointed out also. The question is here:
This is a long post where I outline a problem I faced recently and the steps I went to solving it. I ask for feedback on the solution I came up with and for recommendations for improvements.
Using an FP-growth algorithm in pyspark I created a dataset of correlated keywords for cities around the U...
Because, I have to store a value for the Java, C & C++ combo. If (old solution - check edits) - I have a solution for 3 and only 3 languages where you can have INSERT INTO city_lang_jp_combo (c_l_combo_set, lang_1, p_1, lang_2, p_2, lang_3, p3, c_l_combo_jp) VALUES (1, 101, 1111, 0.91, 2222, 0.92, 3333, 0.93, 0.61) and an FK back to each language and it's p-Value and also a CHECK that the last value c_l_combo_jp was less than p_1 AND < p_2 AND < p_3.
What happens if we want to store 4 languages (i.e. add Python to the mix) - as I have done in the fiddle? Or only have 2 languages (also as in fiddle)... Having said this, I'm totally open to a better way of doing this - my approach is messy and will need a shedload of TRIGGERs to work properly - I've discussed some of the issues in both the question and the fiddle.
java&javascript, fort collins, python, .85
java, los angeles, c++, .65
python&php&mysql, New York, java, .4
This means in job postings in fort collins when both java and javascript appeared, python was likely to also appear with a correlation value of .85
but your design does not store such correlations
Instead of ( (java,javascript), fort collins, python, .85 ), you seem to store ( (java,javascript,python), fort collins, .85 ) (in 3 rows but irrelevant for remark-2)
What I'm saying is that they might have data that correlate differently, eg:
( (java,javascript), fort collins, python, .85 )
( (java,python), fort collins, javascript, .70 )
Which would mean:
> job postings in fort collins when both java and javascript appeared, python was likely to also appear with a correlation value of 85% > job postings in fort collins when both java and python appeared, javascript was likely to also appear with a correlation value of 70%
Got it... however, let us imagine that for ∀ A, B & C, then P(A | B) = P(B | A) and that P(C | A AND B) = P(B | A AND C) = P(C | B AND A) &c... what happens the constraints? :-) It's too early in the morning for Bayes...
Yes, I'm more interested in enforcing constraints as outlined rather than the probabilities actually being mathematically reasonable - apart from 0 <= p-Value <= 1 (fiddle - can't believe I missed that one...). The ledger we were discussing was itself a special case -
in that it involved a strict monotonic sequence of records where there was potential for enforcing inter-record constraints from a single record to the next. I'm looking at enforcing multi-record consistencies (@PaulWhite mentioned enforcing them on VIEWs as well as tables - that would be deadly - pretty much amounts to the same thing as allowing SQL in CHECK constraints or INDEX and FOREIGN KEY definitions) - it looks like it's TRIGGERs all the way...
PostgreSQL's partial indexes give me (some small) cause for hope... and the EXCLUDE constraint seems like good stuff also - never used it - will look into it... Yes, I think that JSONB is the way to go with this problem?
I'll have a look at that later in the week - it's about time I started to try and master JSONB in the server... would you say that JSONB is better than the array type - does the former supersede the latter? As for solving the problem with normalisation, how would you do that?
@bbaird - take a look at the discussion above - how would you impose constraints on that schema? How would you model it using DDL? As I say above, I'd very much appreciate a different perspective...
Just glancing at it, you'd need a "joint probability" entity (you sort of have it implicitly) which would have the probability itself. Normalization error storing it on every row of a many-to-many relationship.
So City -> City Language Joint Probability -> City Joint Probability Language
Probably a better naming convention to be had, but you get the point. Preventing duplicate city/jp/language combos has to be handled through transaction logic, which means passing input parameters as an array/list
*storing it on every row of a one-to-many relationship
It's much easier to enforce the "Joint probabilities add to 1" with the intermediate entity, although again, have to enforce that requirement through transaction logic.
@PaulWhite Good, thank you. I thought things looked different but I'm in a different set of glasses today and couldn't tell if it was me or a better perscription
@bbaird I agree with this. CHECK constraints are oftentimes used because the programmer comes from inferior database that doesn't support CREATE DOMAIN.
Most check constraints are silly workarounds, more so if they do not involve other columns in the same row.
Of course, the great advantage to CREATE DOMAIN is that you only have to pay the cost of coercion or validation once.
