using unique constraints to enable simpler joins

Question

This is a conceptual question as I am considering two approaches to a database design for a set of tables that get joined frequently.

Here is CASE A:

/*SCHEMA*/
CREATE TABLE SCHEMA (
SCHEMA_ID    INTEGER,
CONSTRAINT sch_pk PRIMARY KEY (SCHEMA_ID)
);

/*OBJECTS*/
CREATE TABLE OBJECT (
OBJECT_ID    INTEGER,
SCHEMA_ID    INTEGER NOT NULL,
CONSTRAINT obj_pk PRIMARY KEY (OBJECT_ID),
CONSTRAINT obj_sch_uniq UNIQUE (OBJECT_ID,SCHEMA_ID),
CONSTRAINT obj_fk FOREIGN KEY (SCHEMA_ID) REFERENCES SCHEMA(SCHEMA_ID)
);

/*COLUMNS*/
CREATE TABLE COL (
COL_ID      INTEGER,
OBJECT_ID   INTEGER,
CONSTRAINT col_pk PRIMARY KEY (COL_ID),
CONSTRAINT col_obj_uniq UNIQUE (COL_ID,OBJECT_ID),
CONSTRAINT col_fk FOREIGN KEY (OBJECT_ID) REFERENCES OBJECT(OBJECT_ID)
);

Here is CASE B:

/*SCHEMA*/
CREATE TABLE SCHEMA (
schema_id               INTEGER,
CONSTRAINT schema_pk PRIMARY KEY (schema_ID),
);

/*OBJECTS*/
CREATE TABLE OBJECT (
object_id               INTEGER,
schema_id               INTEGER,
CONSTRAINT object_pk PRIMARY KEY (object_id,schema_id),
CONSTRAINT object_schema_fk FOREIGN KEY (schema_id) REFERENCES SCHEMA (schema_id)
);

/*COLUMNS*/
CREATE TABLE COL (
column_id               INTEGER,
object_id               INTEGER,
schema_id               INTEGER,
CONSTRAINT column_pk PRIMARY KEY (column_id,object_id,schema_id),
CONSTRAINT column_object_fk FOREIGN KEY (object_id,schema_id) REFERENCES OBJECT (object_id,schema_id)
);

A common query that would be run against these set of tables is as follows: CASE A

SELECT *
FROM METADATA_CONTROL.COL
INNER JOIN METADATA_CONTROL.OBJECT ON METADATA_CONTROL.COL.OBJECT_ID = METADATA_CONTROL.OBJECT.OBJECT_ID
WHERE OBJECT.SCHEMA_ID = 101;

CASE B

SELECT *
FROM METADATA_CONTROL.COL
WHERE SCHEMA_ID = 101;

As can be seen, CASE A requires joins, CASE B doesn't. My questions:

-Am I correct that these two table structures enforce the same business requirements? -If they do, how do I determine which case to implement?

I'm weak in understanding the performance gains/penalties of different relational table structures. This is on Oracle 12c.

I appreciate any guidance in this case and some additional resources or rules I can follow with regards to query performance and how they relate to constraints and joins.

Thank you!

Answer 1

Those are not equivalent models.

Consider the following 2 rows in table OBJECT :

OBJECT_ID    SCHEMA_ID
1            1
1            2

You could insert both rows in CASE B. You could not insert both rows in CASE A. Primary Keys are Unique after all - so PRIMARY_KEY(OBJECT_ID) means OBJECT_ID is unique in the OBJECT table.

Now, if you're willing to let the application enforce the "OBJECT_ID is unique" constraint, then you could use CASE B to store data that meets CASE A requirements. You probably should not, but you could. That is to say everything you can put into the database in CASE A you can put into the database in CASE B. The reverse is not true.

So pick between Case A and B based on correctness primarily.

On Case A - the unique constraint is redundant - again looking at the OBJECT table OBJECT_ID is already unique. So you don't need that constraint on your table.

What you probably do want is an index where the leading column is SCHEMA_ID. That could just be an index on SCHEMA_ID, or on SCHEMA_ID and OBJECT_ID. That gives the database a good access path for your typical query where you are providing the SCHEMA_ID. Same goes for the COL table - to get best performance, you likely want an index with leading column OBJECT_ID.

If Case B is correct, you can either add indexes to support your queries, or reverse the order of the fields in your primary keys - if you're always filtering by SCHEMA_ID, you want an index where the first column is SCHEMA_ID. Primary Keys always have an associated index, so you could leverage that.

Basically decide on what is correct per the requirements, then optimize for performance.