Hi Terry, > > So if you've five widgets, two of them Condition=rusty, is that two > > rows? What columns come together to make a unique row rather than > > one that should be merged into another? I'd group those together > > rather than slot Quantity in between, say. > > I had to think about this to understand what you meant by your > question. At the moment Condition could have several entries; New or > Used is the most common, but Needs Assembly also appears (for example, > Pis can come bare or with pre-assembled headers). > > When you talk about rows, do you mean rows in the spreadsheet
You can assume that, and the question still stands. :-) > or rows in the database (is there such a thing)? Here are three ways of thinking about today's typical `relational database', each with their own vocabulary. `Relational calculus' is a set of operations over `relations'. A relation is a set of `tuples'. (A reminder at this point that in maths a set can't have duplicate members, and the members have no order.) A tuple is a set of attribute names each with a value, i.e. a `name: value' pair, e.g. (show: "Swap Shop", postcode: "W12 8QT"). All tuples in a relation have the same attributes, e.g. they all have `show' and `postcode'. Because a set can't have duplicates, attribute names in a relation have to be distinct so you couldn't have (show: "Swap Shop", presenter: "Noel", presenter: "Cheggers"). Similarly, because a relation is a set of those tuples, you can't have two the same. The operations on a relation include... Selection, AKA `restriction', that plucks zero or more of a relation's tuples to form a new relation, e.g. selecting those with an attribute `show' that's `"Why Don't You?". Projection picks just particular attributes, e.g. `postcode', again giving a new relation. Note, since tuples in a relation must be unique, because it's a set, multiple shows with the same post code will be represented by just one tuple. Join takes two relations and an attribute name from each and produces a new relation with one tuple for every pair of tuples in the two inputs where the attributes have the same value. TV shows and their post codes could be joined to post codes and their lon/lats. If a post code has multiple lon/lats then the show appears multiple times in the result. If the post code doesn't have a coordinate, some don't exist on the ground, then the show doesn't appear at all. And then in addition there's the normal set operations of union, intersection, and difference. Union with two relations that have the same attribute names just merges the tuples; no duplicates of course as it's a set. Intersection is those tuples in both relations. Difference is those in one that aren't in the other. Codd at IBM came up with all this and there's lots more that can be built on them that he also covered. Anyway, if you take a relation and write each tuple on a line, with the attributes in the same order, then it looks a lot like a filled rectangle of columns and rows on a spreadsheet. And given the attributes are now ordered, you can remove their name from each spreadsheet cell and make it a column heading. A sheet could have multiple relations as different rectangles. A `relational database' is based on Codd's theory, but quite warped away from it, and likewise SQL is a crude contortion of his algebra. A relation becomes a table. The tuples are rows within it, and columns define the attribute names. Some things remain: a table's rows aren't in any defined order, just as tuples aren't in a set. Other things deviate: multiple rows can exist in a table with the same values for all attributes. SQL's `SELECT...JOIN...WHERE' does the algebra's `select', `project', and `join' in one, with all the clunkiness one would expect compared to orthogonal operators. The `card' approach to a database, with tables, records, fields, and forms, has each record being a spreadsheet or relational-database row, or algebraic tuple, and a field being a column or attribute. Again, it discards some of the purity of the algebra. Mapping it back onto your stock control, a Location relation might have `description' and `lon/lat' attribute names, and your Stock relation would have a `location' attribute name with a value that matched a Location.description. A UI could present a drop-down of those Location.description's. > > Have you considered Google Sheets instead of your LibreOffice > > spreadsheet? It has a Query function that allows SQL-like expressions. https://www.benlcollins.com/spreadsheets/google-sheets-query-sql/ > > Otherwise, like Keith, I was going to suggest > > http://flask.pocoo.org/ given your acquaintance with Python. Django's advantage is more readily defining the web form for data entry from the description of the database table that you've already told Django about. But overall it's a bigger beast and might be harder to grasp what's going on. > They did kill off 4th Dimension though. Not entirely, it seems. Windows and Mac only. https://en.wikipedia.org/wiki/The_Fourth_Dimension_(company) P.S. (A `bag' is a set that allows duplicates; like used for blackballing.) -- Cheers, Ralph. -- Next meeting at *new* venue: Bournemouth, Tuesday, 2018-12-04 20:00 Check if you're replying to the list or the author Meetings, mailing list, IRC, ... http://dorset.lug.org.uk/ New thread, don't hijack: mailto:dorset@mailman.lug.org.uk
