0

In Oracle, I have a table users. Within this table, for every user, I want to be able to store an array of attributes of a user, that is, key-value pairs.

I could create an extra table with three columns—the foreign key pointing to the users table, the key and the value—but I would prefer keeping things simple and don't want to create an additional table for that.

I know that Oracle has varrays, created like this:

CREATE OR REPLACE TYPE example AS VARRAY(20) OF NVARCHAR2(500);

How do I do the same thing, but instead of NVARCHAR2, I would have a tuple (NVARCHAR2(50), NVARCHAR2(200))?

All the resources about Oracle tuples point to either a grouping of columns to be used in a WHERE ... IN clause, or Java-related documentation.

Arseni Mourzenko
  • 50,338
  • 35
  • 112
  • 199
  • What you describe sounds like a variation of Entity-Attribute-Value (EAV) design. Which is probably the WORST design concept ever foisted on the rdbms world. For a good read on this, see https://www.red-gate.com/simple-talk/opinion/opinion-pieces/bad-carma/ – EdStevens Sep 22 '20 at 13:02
  • @EdStevens: Not sure if the article you link is really related to my question. In my case, the `user` entity contains attributes. User's attributes make no sense outside the corresponding user (i.e. it wouldn't make sense to ask oneself who, among the users, have the most attributes, or are there duplicate attributes among the users). Therefore, it makes sense, IMHO, to keep them within the user record, would it be in a form of JSON, or XML, stored more efficiently in Oracle), or a varray of records. If you still think this is bad design, please tell; I'll create a separate question on that. – Arseni Mourzenko Sep 22 '20 at 14:54
  • The linked article wasn't intended to give you a solution. It is a very descriptive account of what happens when you use EAV design. – EdStevens Sep 22 '20 at 15:37
  • @EdStevens: let me rephrase it. My question was about a feature that many relational databases provide today, and that I use—at least in my humble understanding—exactly as expected, in a context where it makes perfect sense. The link you provided, it seems to me, is *completely unrelated* to my question. So either you put it by mistake, because you didn't understand my question, or, what is more likely, I simply don't understand what I'm talking about, and you're rightly pointing a flaw in the design, in which case I would like to know more. So who's right here? – Arseni Mourzenko Sep 22 '20 at 16:27
  • It could very well be I don't understand your question. But when you started talking about "key-value pairs", that's the language of EAV and so triggered my response. Either I'm way off base (wouldn't be the first time) or you are failing to see the forest for the trees in the linked article. Let's just assume I'm off base. – EdStevens Sep 22 '20 at 17:48

1 Answers1

1

What if you use record ... and not tuple?

DECLARE

TYPE t_myrecord IS RECORD ( field1 varchar2(50), field2 varchar2(200));
TYPE t_myarray IS VARRAY(20) OF t_myrecord;
a_myrecord t_myrecord;
a_myarray t_myarray := t_myarray();

BEGIN
a_myrecord.field1 := 'a';
a_myrecord.field2 := 'b';
a_myarray.extend(19);
a_myarray(1) := a_myrecord;
END;
PKey
  • 3,715
  • 1
  • 14
  • 39