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!

user_4142702 · Answer

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.

using unique constraints to enable simpler joins

1 Answers