What is the difference between a multi value database and a relational database?
 

So, what is the difference between a multi value database (Pick) and a relational database? I think I get it but could somebody explain in words of one syllable, possibly with an example?

We have some legacy multi value databases which look like they will need to be converted to relational databases to fit in with the rest of the organisation and I'd like to get my understanding right in my head.

It's very hard to write about relational databases in words of one syllable. :)

We use mongodb which 'might' be a multivalue database (see how much I know about it). It's not relational anyway.

One difference is:

If you want a record of all your customer orders, for example, and you need to filter them by customer then, in a relational database you create an order table with a column called "customer" and then you search for all records with customer = xyz.

In a multivalue database you could create a table called Customer_xyz and add all the order records to it. You can also add records of other customer xyz things apart from orders to the same table. It makes it very easy to find out everything you know about that customer. Depending on the type of data you're managing, the code to access it can be very efficient.

HTH

From https://www.openqm.com/openqm/what-is-multivalue/

Using a grocery receipt as an example:

In a relational database there would be a record for the particular sale: The transaction table would have a unique transaction number, a link to the customer number (in its own table), the date of the transaction, the time, the register, and a link to the cashier (in its own table.) Then a sales table would have, for each item involved in that transaction, the transaction number, the item code, the quantity, and the sales price for the item. To get the receipt you ask for the transaction or search for the date/time and then go into the sales table, the customer table, and the cashier table, gathering the matching data.

In the multivalue database all of these would be in a single table where the list of items would be on one row for the transaction.

I'd say the multivalue database is better for speed and creating applications as long as the overall structure of the operation doesn't change and there isn't much need to do a wide data-validation. In the relational model it is easy to add another characteristic via an additional table and to ensure consistency as the amount of duplication can be eliminated. For example: if someone fat-fingers "Peeches" into a multivalue database there's not a good way to catch that; in the relational database when the transaction is happening, if "Peeches" is entered, there won't be a matching item code and the error can be detected at that time; likewise if "Peeches" was accidentally added to the Item Code table, that's the only place it will appear and can be edited, repairing all future references without changing any other tables.

I cannot say which is definitely better. The noSQL databases can be faster, but SQL compilation is no slouch either.

Pretty much to convert you need to create the tables to expand the multi-values into rows -or- some newer SQL databases offer array elements within row values.

The basic explanation is above. If you have an existing database that you'd like to convert you will end up with a multi-value single table within a relational database system. There's nothing against that apart from the fact that you're not making full use of the capabilities of the system. I cannot comment on speed, that is a different topic.

As for relational databases, here is an example I found through Google (source):

https://cimg7.ibsrv.net/gimg/pprune....f122d4e236.png
The strength of the system is that you can combine data from different parts of the table structure (eg. Name, food, weight) and create new lists or tables any way you like. The weight/height table could be used to record measurements taken over several years but you would not need to list all the particulars for the dog with every measurement as the tag# links it to the rest of the data. Hope this helps a bit.

This might be worth a read https://arq.group/multivalue-databas...nsional%20data.