Open Database Challenge Cup

English
#1

Don’t misunderstand, the challenge is not that I need anybody to justify their preference for databases – I’m seeking advice on whether my application is suitable for building a database AND whether that would easily be of greater flexibility than the spreadsheet I created.

I’ve already asked the Google forum for DB design hints and haven’t yet had time to peruse all 7Billion+ responses. I thought the AskLO forum might provide a tad more focus.

IsItaDB
IsItaDB1535×789 151 KB

Firstly, when the sheet is extended every month, the rows are sorted ascending on B: and all the formulae and formats are simply copied down. The Array O1:R2 provides information facilitating easy extending.

In daily use, the sheet is sorted descending on Col B: and Cols I, O, P, Q, R & S are hidden.

Clearly, Autofilters permit me to select or exclude range permutations of dates, events, times or entered/calculated values AND the Array J1:N3 provides appropriate SUBTOTALS(). Also, three colour conditional formatting is applied to all the calculated cell values which can visually signify the “spread” over the entire column and representative autofiltered selections.

Of special significance is the Array O3:S “the helpers” which circumvent the Countif() type limitations when working in a SUBTOTAL() array and so accumulates only the “Valid” hits in the directly referenced data cells – observe the yellow highlights Q5 & Q56. AND yes, I realise all the helpers accumulate the chosen value in each of the columns – you will notice this is filtered by the C1:E2 Array. I originally had five independent rows – one for each representative column and each column’s helper was defined to target that column – whereby only the defined target was accumulated in O3:S3

Of particular interest is:-

What are the tables?

Is One day a record or just one event?

Should the calculated values be part of the record or calculated by a reporting function?

I appreciate that the “helpers” would be redundant as a report could simply accumulate the target values – IF the calculations are stored with the record!

As you can see, the cluster of 8 rows between the “Final” events represents 1 day of observations.

The Array B6:E is standard and pre-created every month when I extend the sheet.

The Array F6:H is the only data I enter.

Col I is referenced relative to the contents of Col H and provides the value by which Col G must be adjusted.

Col J is the Col K change against the previous row.

Col K is the calculated value of Col G adjusted for the margin in Col I.

Col L is the calculated change against the previous day’s event.

Col M is the daily moving average of the Col K values.

Col N is the absolute change from Initial K to Final K

My justification for the spreadsheet approach is that LOCalc can be defined to expand all the formula cell links and their integrity is retained during sorting and exclusion actions, unless of course there is a range defined within a nested operation – hence the “clumsy” direct references in the Final Cols M&N.

AND

Everything is in real time, with some charts – which have been moved out of sight – responding immediately to any change or autofilter.

#2

A spreadsheet is not a database, not even close, not even similar. The database you dump on a spreadsheet is only in your head.

#3

But every time I ask a question about a formula or function in my spreadsheet you tell me I should be considering a database.
To paraphrase and simplify my question “How do I best consider a database to provide the functionality of this example spreadsheet?”

#4

Your screenshot looks like a horribly complex way of keeping track of things. I have no clue what you are doing. It looks like the usual spreadsheet trap with storage, processing and output merged in one colorful lump.

#5

Perhaps you should have led with that

Incredibly effective and easily completed, with the ability to analyse from probably every perspective. Perhaps not a purist’s delight but I can see at a glance what has happened and more importantly - what events have the most significant impact on the end result and any definable point on the way.
It was never intended as a forecasting tool, it simply harnesses the power and flexibility of a spreadsheet to present the information in an easily assimilated manner.
I’d guess it’s unsuited to a database solution.

#6

Hello,

I indeed agree with @Villeroy - difficult at best to determine what you may have there. Even reading you description, I gave up when I got to:

as Clearly seems a bad choice.

This is a question and answer site and it appears many question are presented in your question.

#7

The inference being “Clearly, Autofilters perform the function for which they were intended”

Clearly, there are a lot of questions asked of a question and answer site and I believe you wasted your time if, following your misinterpretation of one word at the beginning of that question - long before any information could have been assimilated - you didn’t bother to fully read the question. Would you disagree?

#8

The entire question was formulated because every time I ask for advice on the utilisation of some of the functionality I am unsure of, @Villeroy advises me that I should be using a database. I clearly thought I would provide as much of a description as possible and highlight how I thought the spreadsheet was providing a usable solution. I enter three data elements per line and the spreadsheet provides all the analysis I think I need. I was keen to understand how a database could surpass that. I was clearly looking for assistance from a question and answer site - not dismissive denigration. I can go to my children for that :wink:

#10

Do not agree. Although did not “read” all, did glance through and saw multiple questions:

#11

Point is, do not comprehend what you have and what you need. It is not clear at all. Items like this are not helpful in my opinion:

#12

So in all fairness, I sat here and read the entire question, some parts multiple times, trying to figure out what it was all about. Alas, my initial thoughts are the same. Have no idea what you have here. Something about Events but not sure what Challenge Cup has to do with anything.

Don’t understand what is entered and when or how you get all those repeated events and times.

All these items of this column is something of that column makes no sense at all.

Images are not useful in my opinion. It would be better, as noted before, to simply describe what you are dealing with and what results you expect.

#13

I was just reading a book on code generation, and the mantra was that for 99% of production work, data are data–practically anything can be put into a relational database, although a few things might be better stored directly in tree constructs like XML. Yes, this can go into a database.

What I think deep down you might be asking is usually phrased: “How can these data best be normalized?” Normalization is the process of distinguishing fields so that no field shares data (explicit or implicit) with another field, every record contains fields that serve as a complete covering of the needed data, and as little “helper” data or “tie point” data is used as possible–by far preferably none at all–which is what puts databases at odds with a lot of OOP concepts in spite of all you hear about data encapsulation, etc. You can look up Codd’s Rules, but they’ve been added on to for completeness nowadays to the point that they may be more confusing than they are worth.

I remember looking at the ‘hidden columns’ a while back for setting them up as array formulas. Definitely there is nothing about what I recall that was not in keeping with fundamental data selection and aggregation in a relational database system. J1:N3 would probably be done in business logic, which could just be code-behinds for forms in something like LO Base. One big question to ask is, “Who uses this?” If it’s only you, I doubt conversion would be worthwhile. If it’s others, then remember that retraining will be a significant part of the overall conversion process, perhaps 25-30% of it. I did an Excel-to-Access conversion a few years back, and have since come back to see that everyone just opens the data tables and edits like it were still Excel. Ugh.

1 Like
#14

It’s called “flippancy” one of the records in the English database of light-hearted humour. I keep forgetting - the interpretation of humour is nationalistic.

I thought the screengrab identifying that the “template” items were daily, timed and referred to the eight specific named events would probably be sufficient edification. I was wrong. Specifically;
For insurance purposes, I was asked “What is your BMI”. like you, I had no idea. When I investigated, I discovered that our weight should be aggregated over a period of time as it changes frequently. So, I monitored my weight for the eight events identified in the template and was amazed at just how much it varies during the day - let alone over a period of a week.
I first monitored the weight changes with a simple list in LOCalc but was so intrigued that I started adding functionality to the list and calculating the event and daily variations. I realised that in accordance with good documentation structure practice, filtering and inverting the list so I could always see the latest (most pertinent) events close to the “management summary” or “dashboard” would produce a more easily interpretable and therefore more efficient structure. Over the period, I added all sorts of “bells & whistles” simply because it amused me.
My question concerning “Tables” was simply to try to understand how the “professionals” would take the “template” ID, DATE, DAY NUMBER, EVENT NAME, and record the three moving values TIME, OBSERVED WEIGHT, CLOTHING ADJUSTMENT FACTOR, and then produce a report or enquiry or pivot table and/or charts that presented me with the same level of analysis and display as is inherent within my

Incidentally, the “Frames” I have placed around the various “Arrays” were simply to focus attention on those “arrays” as I was mentioning them within the text - they are not present in the product.
If you ask me nicely, I could even provide a copy of the full working sheet so you would be able to assimilate what I have clearly so poorly defined. The only personal information it contains is my weight at various times throughout the last 61 weeks and four days accounting for 3449 events - AND Growing :rofl:

#15

Thank you for your insight and vision, you are absolutely “spot on”. I am the only user. Not quite sure what all the technical terms imply but I promise you, I wouldn’t open the tables. Once my three transaction items are processed it’s “set in stone”. In fact, with the exception of the “helper” cells, I regularly simply cut and paste special “values only” on much of the structure so I don’t recalculate 20000 cells every time I touch the enter key. I know LOCalc can cope with that tiny volume but to ensure I dont lose the input during a power outage I actually exported it to GSheets and perform the event processing on-line. Sheets Sucks, but then again, LOCalc is still a nightmare to try to save and open documents stored on a GDrive. :man_shrugging:

Thanks again - I learned a couple of things

#16

I probably didn’t envisage replacing the Calc with a DB in a production capacity - it’s just that I kept hearing “You’re making a pig’s ear of it, you should be using a database” that I thought to myself - “You’ve got all these rows, why not use them as the source data for a DB experiment?”
Hopefully, I could then learn a couple of new things.

#17

Incorrect assumption. I’ve known about BMI for decades. I could not determine that was what you were dealing with here especially with the misleading title of the post.

As for a database, most who are familiar with Base should see little problem in creating one. For someone without working knowledge of Base it will be a task. Also transferring existing data will be needed.

#18

You completely missed the point.
I wasn’t dealing with BMI, that I could achieve with one simple formula and I implied that I had no idea what was my BMI at the time - primarily because I didn’t even care what was my weight at that time. I imagine you will still have to calculate your BMI after hopping on the scales - so my assumption was far from incorrect.

I was dealing with the intriguing nature of everybody’s weight change throughout a 24 hour period
I was simply seeking insight into the nature of normalisation and some hints as to which parts of my “template” would be considered the record. Should one record be one existing row or should one record be one day with the value for each of the existing “checkpoints” stored in that single record in a sequential order reflecting the event chronology?
In all honesty, I would have thought labelling things like “pre-breakfast”, “post-breakfast”, pre-dinner" with values like 74.5, 75.1, 75.3 would probably give a gentle hint that weight was being measured on a regular basis.
I guess I was wrong. In any event - Joshua provided the insight I sought.

#19

BTW, @WhiteKnight, “pig’s ear” is a recursive relationship in a class diagram.

pigs-ear
pigs-ear960×720 32 KB

And someone wants you to figure it out… Referential integrity, anomalies of data deleting, inserting and updating, and so on. That’s a whole theory and it takes practice.

Edit: If you are the only user, then you can organize everything using a spreadsheet.

1 Like
#20

Nope. Incorrect again. Easy to remember if good or not based upon current weight. Personally I don’t care as haven’t thought of this in years.

Which I thought was to convert something you had to use database. I seldom ask the persons motivation for doing a particular task as many may be questionable to me.

With “Events” seemed as something which occurred at the event. The numbers meant nothing to me. I deal with pounds.

#21

British, informal. : to do or manage (something) badly He has made a pig’s ear of his reelection campaign.
Is it safe to assume that the recursive relationship definition has the same interpretation? It certainly looks from your diagram that it’s going around and around in an endeavour to disappear…

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.