-1

We need to store generic information (every entry has this information) and client-specific information (each client has its own information). The generic information is straightforward in SQL, but I don't know how to tackle the specific information as there are a different number of columns and different column IDs.

Bad idea #1:

Create a new table for each client for its specific data, then do a join when wanting to use that data. This is a bad approach as it adds a lot of overhead for maintaining the database.

Bad idea #2:

Create a new table and have 3 columns: client ID, Key, Value. Each row in this table contains some specific key (column ID) and value for a client. This is bad because the data types can't be known (everything will be varchar) and this is still very hard to maintain.

Other ideas:

MySQL supports NoSQL (version 8 and beyond). Can you combine data from a NoSQL DB and a SQL DB?

PostgreSQL adds "object-oriented concepts". Will PostgreSQL store client-specific data alongside generic data?

Example:

Table for generic data:
shapes {Name,number_of_sides,area}

Specific attributes:
Square {height, rotation}
Rectangle {height, width, rotation}
Elipse {focal_a, focal_b}

All of the shapes have a name, number of sides, and an area, but depending on the object, there are additional properties like height, width, rotation, etc.

philipxy
  • 14,867
  • 6
  • 39
  • 83
OM222O
  • 310
  • 2
  • 11
  • 1
    This is too vague. Some sample data would help to establish the requirement. – Tangentially Perpendicular Dec 12 '22 at 19:33
  • You could dump it into a json-object and store it in a column of data type jsonb. That will give you some structure, many json-functions and also index options. https://www.postgresql.org/docs/current/datatype-json.html – Frank Heikens Dec 12 '22 at 19:43
  • If you go for a hybrid design (relation + JSON) then Postgres is a better choice than MySQL in my opinion. Note that hardly any of the problems described in Bill Karwin's "How to Use JSON in MySQL wrong" apply to Postgres. But Postgres is still a relational database. So anything that can be achieved with a properly normalized relational model should be preferred over a JSON approach - except when implementing the EAV anti-pattern. –  Dec 12 '22 at 20:33
  • [How can you represent inheritance in a database?](https://stackoverflow.com/q/3579079/3404097) [More](https://stackoverflow.com/q/190296/3404097) [How to design a product table for many kinds of product where each product has many parameters](https://stackoverflow.com/q/695752/3404097) [More](https://stackoverflow.com/a/2945124/3404097) [And more](https://stackoverflow.com/q/5106335/3404097) [Re EAV](https://stackoverflow.com/a/23950836/3404097) etc etc etc [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [mre] – philipxy Dec 12 '22 at 23:51
  • What is your 1 specific researched non-duplicate question? PS What does your research show that is relevant to your question? PS What published DB design textbook/reference/presentation are you following? What is the design method? What are its steps? Where is the first place you are stuck? Why/how are you stuck? What relevant design do you have so far? [ask] [Help] – philipxy Dec 12 '22 at 23:54
  • [Strategy for “Which is better” questions](https://meta.stackexchange.com/q/204461) – philipxy Dec 13 '22 at 00:09

1 Answers1

1

MySQL's NoSQL interface is called the X DevAPI. Data is stored in JSON documents in InnoDB tables. The X DevAPI is used over a different protocol. You can't mix SQL queries and NoSQL queries on the same connection, but you could in theory use both an SQL connection and a NoSQL connection from the same client application.

In SQL you would use the SQL JSON functions. That's pretty hard to write efficient SQL queries for JSON. Just browse some of the questions about it on Stack Overflow about MySQL and JSON (see the tag ). Or read my presentation How to Use JSON in MySQL Wrong.

Your first two ideas you describe above are probably going to lead to tears.

The second one is , which has been discussed a lot on this site. I wrote a blog on it: EAV FAIL, and a chapter on EAV in my book SQL Antipatterns, Volume 1: Avoiding the Pitfalls of Database Programming.

Your first idea is also a chapter in my book, titled "Metadata Tribbles." I recommend in general to allow users to generate new data, but not new metadata (in other words, no new table per user).

Allowing semi-structured data is always at odds with relational database design. You're fighting against the advantages of RDBMS. So any solution is going to be more or less awkward and feel like it's "wrong." You just have to pick a solution that you can live with.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks for the reply. yes, I know the two ideas I suggested are really bad (hence the titles). I will have a look at the links, documentation, and JSON data type to see if that can somehow be manageable. As of now, the number of clients is very limited (less than 10, and they're not frequently added), so the first idea isn't too horrible yet, but as you said, all of these solutions feel wrong. – OM222O Dec 12 '22 at 19:55