Skip to Main Content

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Chris Saxon

Thanks for the question.

Asked: September 23, 2025 - 6:19 pm UTC

Last updated: September 29, 2025 - 2:46 pm UTC

Version: 19.28.0.0

Viewed 100+ times

You Asked

Hello,

I work as a dba and get requests from developers to create tables which we often review and sometimes change for better design and/or implementation.

One of the developers recently sent a request to create a table such as the following ;

table_name:t1_relation

Column_names:
c1_master_id_pk (foreign key to t1_master table)
c1_attribute
c1_value

primary key all 3 columns.

They explained that the data in all these columns are all non nullable and that they are all needed to uniquely identify a record.

having all the columns of a table as a primary key didn't look very right to me and so
I suggested we create a surrogate key and make the 3 columns unique with not null constraints on all of them. they initially said yes then came back and said to change it to be how they requested initially.

I'm messaging to ask if this is proper database design and what would be the ideal way to implement this?

and Connor said...

There is no inherent issue with a PK being all columns in a table. For example, its common in intersection tables:

- Students take many classes
- Each class has many students
which will give rise to:

STUDENTS table ( primary key STUDENT_ID)
CLASSES table ( primary key CLASS_ID)
STUDENT_CLASSES intersection table (columns = STUDENT_ID, CLASS_ID, and primary key the same)

But !!!!!!!!!!!!

Based on the description you've provided, unless you've simply anonymised things for the question, it sounds a lot like a generic style data model.... and that can be very very bad news.

Check out these asktom posts - I'd encourage you read the "Bad Carma" one :-)

https://asktom.oracle.com/ords/asktom.search?tag=query-on-design

https://asktom.oracle.com/ords/asktom.search?tag=data-models



Rating

  (3 ratings)

Comments

A reader, September 24, 2025 - 10:02 am UTC

Things have been anonymised for the purpose of the question
Connor McDonald
September 25, 2025 - 6:27 am UTC

phew :-)

Fears and suggestions

Stew Ashton, September 24, 2025 - 10:05 am UTC

Once I was tasked with "approving" a similar design. I said, "I don't want to OK this, but I will if you promise you will always search on master key and attribute!" They were quick to promise, and almost as quick to start searching on attribute and value. Wretched performance!

If you do this, I suggest:
1) Index-organized table
2) primary key only on master key and attribute, unless they really want multiple values for the same attribute.

As an alternative suggested by the Oracle JSON team, put a JSON object in the master table as a "flex field". All the attributes are there in the master row itself, and an attribute can have an array of values. As searches on attributes appear, JSON indexes can be created to optimize the queries.

Revised thoughts

Stew Ashton, September 25, 2025 - 8:21 am UTC

I didn't see the "things have been anonymized" comment until I submitted my own comment 3 minutes later. I'll start over.

1) Primary keys are supposed to be immutable. A row consisting entirely of primary key components should not normally be updated. Has the developer promised that? Your proposed solution would be more appropriate if updates are expected.
2) The "intersection table" Connor mentioned has primary key components that are foreign keys to other tables. The foreign keys reference immutable primary keys in those tables. How many of the requested primary key components are foreign keys?
3) The request would allow multiple rows with the same values in any two of the three columns. Is that really what the developer wants?
4) An index-organized table would be worth considering.
5) As with any index on more than one column, search patterns should be considered when deciding on the order of the columns.
Chris Saxon
September 29, 2025 - 2:46 pm UTC

Good questions Stew.