how to represent one-to-one relationship in db design?

Question

Say I have two classes Laptop and Lcd. Let's now assume each Laptop can have only one Lcd and an Lcd can only be installed on one Laptop at any time. Let's also assume an Lcd can be removed from one Laptop and installed on another. So this is a one-to-one association from Laptop to Lcd.

Now the question is, how to design the db schema for this one-to-one relationship?

Many thanks,

Bryan

Answer 1

This can be achieved by having a foreign key in the Laptop table pointing to the primary key of the Lcd table. A primary key is usually an auto incremented integer and ensures that each row in a table is unique.

Answer 2

The easiest way to model this would be to place a foreign key in the LCD table that links back to the Laptop table. Make that column non-nullable and unique.

Laptop
-------------
LaptopId (PK)
...

LCD
-------------
LCDId (PK)
LaptopId (FK, Non-null, Unique constraint)

(It's also worth noting that this same approach would work the other way around--with the Laptop table having a non-nullable unique foreign key to the LCD table).

Answer 3

You simply have a column in the laptop tables that references its lcd's primary key without a join table.

You didn't specify which database software you're using, but in MySQL, this would look something like this:

CREATE TABLE laptops (
 id INTEGER NOT NULL AUTO_INCREMENT,
 lcd_id INTEGER,
 PRIMARY KEY (id)
)

CREATE TABLE lcds (
 id INTEGER NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (id)
)