Dont ever pass a complex query to mysql unless you are going out for a lazy lunch,
Its great at simple queries but total pants at complicated ones compared with say Oracle.
which is why a lot of big websites run Mysql but banks run oracle.
Horses for courses.
yes, but I think he wants more to play with data than store it efficiently or have different ways to access it.
umm pf course libre office already has a sql database in it but no idea how that works with the calc stuff.
I'll add,
The Kimball approach to data warehousing, dimension modelling etc...
Me head freaked out after years of rotten coded heavy duty Access reporting databases that could have been done a whole lot simpler with some forethought on what the useful outputs would be, rather than fashionably normalising everything and giving your CPU a bad headache presenting calculated results. Check out a very readable book by Kimball/Ross, "the data warehouse toolkit".
Oh, stay well away from Access.
Spreadsheets are good at graphics, but the data side is a pain.
For example, I load in a data table. I want to select a subset of rows where column L matches an expression I want to group those by the values in column S I want to ignore those values that are text or less than 57 I want to compute the median of column F for each group of column S
Now I import a new table and want to use a function on that to provide the expression to match in column L.
In other words I want to write: fn(x) = median(F,delete(group(S,subset(L==x(L))),
I got quite interested in databases a few years back, and two things quickly became obvious. Spreadsheets make good flat databases but most things need a relational database. Deciding which category your data falls in is lesson one.
The second thing (confirming your observations) is that database design is best kept away from programmers. They will code the thing to death to avoid admitting the design is wrong. The example I remember is the poodle parlour. Is the dog or its owner the "entity"? Neither probably. I enjoyed the thinking part but couldn't do the hard work :-)
Or driving the spreadsheet from eg a VB script via COM?
See eg:
On 13/04/14 10:04, stuart noble wrote: The example I remember is the
I say both are. With a 1 to many relationship.
Or a many-many if you wish to register both partners with seperate accounts - though that seems unlikely to be useful.
Unlikely scenarios are always cropping up IME. Different people might pay for the same dog on different occasions. I found the Northwind Traders sample database a good learning tool
You are quite right, that would be a prime candidate for a "proper" relational database, with the caveat that it needs to be designed by an expert and whether that is justifiable depends on the amount of information held. Probably not appropriate for 100 cars, but would be for 100,000.
An example of what well designed databases can do is shown by "Purchase History" in eBay and Amazon. If you think about the number of transactions which they have (billions?) and see how fast Amazon can tell you "You bought one of these on xxxxx, do you want to buy another, the price has come down 17%" you are seeing well designed relational databases in action.
I wouldn't want to discourage you from having a go, but would suggest "starting simple".
It's interesting how simple (and how similar in essence) most databases are. One customer, many invoices, each containing many products. One dentist, many patients, each having many appointments. It's just about how many layers you need. If each appointment is broken down into products used, procedures carried out, that's an extra layer of complexity. Pretty much anything an "expert" comes up with is likely to be reinventing the wheel to a degree. There are so many free sample databases out there that you can almost certainly find one to modify for your own requirements. I've seen some truly awful Access company databases, laden with bells and whistles but not a relationship in sight.
Or a google docs spreadsheet driven from javascript scripts in the background... (gives you multiuser concurrent access for free)
HomeOwnersHub website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.