Re: The joy of SQL

On 11/3/24 6:30 PM, Lawrence D'Oliveiro wrote:

On Sat, 2 Nov 2024 21:48:47 -0400, 186282@ud0s4.net wrote:
 

MV = Multi-Valued = Data fields that can hold long lists of that KIND
of information. If you have a fleet-tracking DB then there'd be ONE
field saying "mileage" and likely another for "fuel-added" and they'd
just be added-to over time.

 That’s just a single total in each field.

   It's every like-value in one field - a 'list' so
   to speak - divided as sub-values - but sub-sub-
   values are also legit.

The record for that vehicle is read all-at-once, rather than there
being dozens, hundreds, of records for said vehicle every time it
was fueled-up.

 This is why you have joins: so that you can retrieve multiple related
records with a single query. This is the proper relational way of doing
it.

   Why join when the MV approach keeps yer data
   in an already-joined state ???
   You just read straight across. The RECORD is
   "Vehicle_Number". Inside we have lists (fields)
   for date, mileage, fuel, comment. Date[1] goes
   with Mileage[1] goes with Fuel[1] goes with
   Comment[1]. If you want fuel mileage you add
   a calc field.
   Python, and to some extent FORTRAN, have very good
   string list-manipulation/reading/writing/insertion/deletion
   capabilities ... it'd be fairly easy to set up a MV DB in
   those languages.
   The old PICK-ish DB I spent a decade with used pure
   ASCII-delimited fields (this was pre-unicode, you
   could bring 'em up in a text editor and see all the
   old IBM-BIOS high-ascii symbols).
   255 separated records, 254 separated fields, 253 separated
   sub-fields, 252 sub-sub-fields and so forth - thus you
   could have nearly 200 sub-sub-sub-sub-(etc)-values to
   the data if you were that insane. Going deep programmatically
   for custom sorts and such could be useful however.
   I think PICK wound up being written *in* PICK for the most
   part... a compiler for a sort of BASIC was included so
   PICK would up being writ in PICK-BASIC. The version I
   used DID have some key functions writ in 'C'/ASM though
   for top speed.