0

I need to relate groups of records, like I have 100 records in table, 3 of them should be known they are related, and other 5 are related to own group. I don't need any group IDs or anything like that, I just need easy way to all records bound to given one.
Additionally, only very small part of records will need this, so I tried avoiding additional fields in table, but failed so far.
My best guess would be storing group id in the main table and somehow keeping track of highest group id used to not break anything (getting max value would work probably?)
It seems messy and somehow like too much overhead to me, I'd appreciate any suggestions

EDIT: my main table contains autoincremented unique id for each record, and rest of table might be considered blob, it is using innodb for now I need to bind records created as batch, but I'm afraid it might be needed to bind already existing records (and I don't really want to mess too much with code maintaining the database, so I though of first creating all records, gathering their ids and then binding them)

EDIT2: I'm using ORM to manage DB records (Eloquent to be precise), so my question was purely about DB structure and I may even decide to use secondary table to store bulk ids and then create new bulk entity and then create new records with bulk id set for them

| improve this question | |
  • can you edit your question and add the table structure and sample data we see how you want these records related? That will help us try to find you a solution – indago Apr 6 '16 at 5:49
  • @indago currently there is absolutely nothing that would connect these records, I just need to know which ones were added as a bundle, but in most cases they are independent, it's meant to allow batch removal of records added in batch, current data and table structure is irrelevant to that, but of course there is autoincrementing unique id field, and the other fields could be treated as single blob in this given problem – zakius Apr 6 '16 at 6:03
  • if i can get you right you mean the records are inserted to the table in two ways, from a bulk insert and from a single insert. So you want to know the records that were in the same bulk insert right? – indago Apr 6 '16 at 6:06
  • @indago exactly that, but nothing more, so I don't think that creating table for bulks is that good idea... but may be cleaner than selecting max batch id from main table I guess... – zakius Apr 6 '16 at 6:24
  • The bit about bulk inserts seems to be an integral part of the problem at hand, because it explains when/why rows need to be related. So please consider editing that in to your post. – Andriy M Apr 6 '16 at 6:49
1

Ok, so as I forgot about one more important thing: I use soft deletes, I decided to:
- Create new table containing only autoincrementing unique id and nullable datetime containing date of soft delete
- Add nullable foreign key linking my main table to the new one with ondelete cascade to sort out hard deletes
- When batch adding create new records first create the bundle entity and link them to it on creation
- Make my bundle entity cascade soft deletes to all related records
- When (soft) deleting entity from main table call proper function on bundle entity (if exists) or main entity otherwise

This way everything works as I expected, but the extra table still hurts my eyes and I don't find it the solution, just something that works, but I wanted to avoid

| improve this answer | |
  • If you need to group your rows (i.e. keep this information in the database), that's the way to go. A group_id (or whatever you want to name it) which is a foreign key to another table. Nothing to hurt your eyes here. – ypercubeᵀᴹ Apr 6 '16 at 11:27
0

You can get the number of the rows that were inserted by executing the following query SELECT ROW_COUNT();

Then you can get the latest id from SELECT LAST_INSERT_ID()-1;

Assuming the row_count result for example being 2, and last_insert_id being 8956. The group of two rows will be with id 8956,8955.

Hope that helps you out without any structural changes to your table or database.

| improve this answer | |
  • 1
    From the manual: "If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only." (emphasis preserved) – Andriy M Apr 6 '16 at 7:38
  • @AndriyM, that's why we need to use Select ROW_COUNT(); to get the number of rows inserted then since the ids are sequential we can get the other inserted rows from the one returned by LAST_INSERT_ID(); – indago Apr 6 '16 at 7:49
  • I edited my question again to clarify my needs, I'm asking only about the structure as I won't be using raw queries for that (or at least not only raw queries I can create myself) – zakius Apr 6 '16 at 8:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.