MySQL PHP, table inside table (multiple rows per entry)

Question

I have a setup shown below.

On my webpage, we are looking at DOCUMENT #83156. The user made multiple entries in the document. I will have thousands of these DOCUMENT numbers, and some may have no entries, and some may have 20 entries of Item/Changes. Each "entry" into my database is according to a document number.

In this example the user made 4 changes to the document. I'm trying to store all of these changes into a single entry (or per document number).

I DO NOT want to have duplicate numbers of the DOCUMENT # (meaning I do not want 4 separate entries in my database for this example).

Does anyone know how to achieve this with MySQL? I've heard that MongoDB has a simple way of doing this. I have zero experience with MongoDB, but if it would be easier I might take a look into it.

Any advice is greatly appreciated.

enter image description here

you mean something like select distinct ...? However, note that storing multiple distinct bits of data in a single record/field, the way you want, is considered bad practice in databases. Every unique bit of data should have its own field/record, so that the DB can easily associate it to other things. When you smoosh multiple things into a single field/record, you remove that associative ability, which somewhat negates the purpose of using an associative database in the first place. — Marc B
I was thinking of storing the data per cell but each string is separated by a special system of characters (ex: :>: ) and then have php parse the data from each block according to those characters. — bagofmilk
If any of those bits of data are refernces to other tables, e.g. foreign keys, then you're in for a world of pain. a db can easily compare "7" against another table during a join operation. But if you have "7,8,9" in the field, then it cannot do anything — Marc B
nosql databases like mongo are built/tuned for certain types of database operations. Since they specialize in those operations, they can be faster than a general purpose SQL database. But the cost of such a specialization is generalization. If you ever end up needing to something that falls outside of the design boundaries, you end up suffering for it, and end up needing to have used a general purpose database from the get-go. Nosql are great at what they do, but they're not magic "your sql is slow? nosql will fix that!" bullets. — Marc B

Eugen Rieck Eugen Rieck · Accepted Answer · 2013-11-07T22:05:16

MySQL is a relational database - and what you want is a simple relation:

Use the table structure as given by you (red border) for the documents, record the 'CHG' Actions there
Use another table for the changes:

.

CREATE TABLE changes (
  ID INT PRIMARY KEY AUTO_INCREMENT,
  PARENT INT NOT NULL, -- consider foreign key constraint here
  CHGTYPE ENUM('Add','Del') NOT NULL,
  CHGTXT VARCHAR(250) NOT NULL,
  REV_WAS INT, REV_NOW INT, QTY_FROM INT, QTY_TO INT
)

where PARENT stores the parent change (83156 in your example)

MySQL PHP, table inside table (multiple rows per entry)

1 Answers