Geek asks and answers

Composite Key Composite Keys

I am trying to figure out which approach to take for the data model that I am creating. I have two tables that I originally created using compound keys. Now I am adding a third table, which is the join table of the previous two, which will lead to the creation of a composite key of three fields and two foreign keys with two fields. It will probably be in MySQL and will be used along with some kind of Java persistence framework. I always liked to use compound keys because it seems like a more natural way of presenting data, but I want to make sure that I don't tune into the world of pain later. Should I continue to use the mentioned approach or just create some automatically increasing identifiers in tables?

+3
source share
3 answers

Hibernate recommends using purely technical, automatically generated, non-composite keys (with the exception of clean join tables). And for very good reasons IMO.

Your mapping will be much more complicated with compound keys. Performance will be reduced due to more complex indexes. General programming will be more difficult, because instead of identifying a given object with a long period, you will need two or three longitudes (think of URLs and forms, for example, where you will need to put two or three parameters / hidden fields for your URLs -addresses / forms).

, , , , , .

+2

-- ( ) , .

(, ), , ORM , id .

+2

, . 6 .

:

CREATE TABLE semester (semester_id INTEGER PRIMARY KEY, semester_name VARCHAR(40));
CREATE TABLE student  (student_id  INTEGER PRIMARY KEY, student_name  VARCHAR(40));
CREATE TABLE subject  (subject_id  INTEGER PRIMARY KEY, subject_name  VARCHAR(40));

:

CREATE TABLE enrollment (
  enrollment_id INTEGER PRIMARY KEY,
  semester_id INTEGER NOT NULL,
  student_id INTEGER NOT NULL,
  room_number INTEGER,
  FOREIGN KEY (semester_id) REFERENCES semester (semester_id),
  FOREIGN KEY (student_id)  REFERENCES student  (student_id),
  UNIQUE INDEX (semester_id, student_id)
);

-- similarly ...
CREATE TABLE class(class_id ..., semester_id ..., subject_id ..., class_number ...);

. :

CREATE TABLE grades (
  student_in_class_id INTEGER PRIMARY KEY,
  enrollment_id INTEGER NOT NULL,
  class_id INTEGER NOT NULL,
  grade char(1),
  FOREIGN KEY enrollment (enrollment_id),
  FOREIGN KEY class (class_id),
  UNIQUE INDEX (enrollment_id, class_id)
);

:. , , , ? ( : )

- UNIQUE FOREIGN KEYs, PRIMARY KEY :

CREATE TABLE enrollment (
  semester_id INTEGER NOT NULL,
  student_id INTEGER NOT NULL,
  room_number INTEGER,
  PRIMARY KEY (semester_id, student_id),
  FOREIGN KEY (semester_id) REFERENCES semester (semester_id),
  FOREIGN KEY (student_id)  REFERENCES student  (student_id)
);

-- along the same lines...
class(semester_id ..., subject_id ..., class_number ...)

:

CREATE TABLE grades (
  semester_id INTEGER NOT NULL,
  student_id INTEGER NOT NULL,
  subject_id INTEGER NOT NULL,
  PRIMARY KEY (semester_id, student_id, subject_id),
  FOREIGN KEY (semester_id, student_id) REFERENCES enrollment(semester_id, student_id),
  FOREIGN KEY (semester_id, subject_id) REFERENCES class(semester_id, subject_id)
);

, .

: semester_name, student_name subject_name ( , ). , , , , .

, : ? , ?

. Hibernate . DataMapper Ruby.

+2

More articles:


All Articles