0

I want to create a table that will act as a lookup table for an application.
The format I have in mind is something like:

CREATE TABLE Associations (
  obj_id int unsigned NOT NULL,
  attr_id int unsigned NOT NULL,
  assignment Double NOT NULL
   PRIMARY KEY (`obj_id`, `attr_id`),
);

Anyway I have the following question:
The idea is that this table will be loaded/used in the application level as a map:
obj_id -> listof(<attr_id, assignment>)

The obj_id refers to another table which is huge. Now this means that Associations will be x times the original table since we'll have multiple rows for each obj_id.
Taking into account that I don't really need to SELECT in the table by assignment and at this point I also don't need to SELECT by attr_id is there another design approach for the table that could be more efficient?

Jim
  • 3,845
  • 3
  • 22
  • 47
  • Not completely sure what you mean by SEARCH in thi context – RiggsFolly Nov 04 '22 at 10:45
  • @RiggsFolly: I meant is `SELECT` I updated the post – Jim Nov 04 '22 at 10:47
  • Still a little confused. I assume this is a simple cross reference (link table) so if you have a obj_id you can find the associated attr_id(s) and if you have a attr_id you can find the associated obj_id(s). So if you dont need to select on assignment or addr_id then all you have is a table of obj_id's which you already have in the HUGE table but I hope as they are indexed on obj_id I am lost as to the use of this cross reference table – RiggsFolly Nov 04 '22 at 13:38
  • @RiggsFolly: I need to SELECT/retrieve all the `attr/assigments` for each `obj_id`. The `attr_id` and `assigmnent` do not refer to any other table. It is data stored associated per `obj_id` – Jim Nov 04 '22 at 13:54
  • Not data that also exists in the huge table I hope – RiggsFolly Nov 04 '22 at 13:55
  • @RiggsFolly: No they don't exist in the huge table. – Jim Nov 04 '22 at 13:56
  • @RiggsFolly: I was thinking to e.g. store per `obj_id` a json object for the list of `` per `obj_id` but I am not sure if that is a good way either – Jim Nov 04 '22 at 13:56
  • well if all you need to do is `SELECT attr_id, unsigned WHERE obj_id = ` then its fine, and you only really need to index the obj_id – RiggsFolly Nov 04 '22 at 13:58
  • Be very careful with JSON, it fine for data thats only ever retrieved, but as soon as you want to query based on the existance of something in the JSON, its gets overly complicated – RiggsFolly Nov 04 '22 at 13:59
  • @RiggsFolly: Yes I would only need to do `SELECT obj_id, attr_id, assignment WHERE obj_id IN (.....large number of values)` to create the mapping in the application. The fact that the table will have a huge number of rows made me think if there is a problem with this approach – Jim Nov 04 '22 at 14:05

0 Answers0