Is GUID a good reference key for addresses in this case?

Question

I am currently working at a construction management software and I stepped into a problem (described below) when designing the schema for my database which contains four tables, for now, i.e employee, address, user and license.

The tables that created the confusion are employee and address; code provided down below.

In order to maintain the Physical integrity of my data, I did not store anything lexically derived from address, such as Address1, Address2, City, State, County etc in the table employee; since I subjectively considered that it would be better for attributes that do not uniquely identify a specific employee to make part of another table. Now my question arises:

• Is it a good choice to use a GUID as the primary key for the address table?
• If yes, is there any possibility that it may be a factor which prevents fast querying?

The reason why I have chosen to use a GUID as PK-indexing is that I was left with no options. EmployeeId from address does not offer me a solution since I can not have a field being PK as well as FK: see this.

CREATE TABLE employee(

    employeeId  INT
                NOT NULL
                CHECK(employeeId > 0),

    firstname   VARCHAR(20) 
                NOT NULL,

    lastname    VARCHAR(20)
                NOT NULL,

    sex         VARCHAR(1)
                NOT NULL,

    birthdate   DATE
                NOT NULL,

    addressId     VARCHAR(30),

    PRIMARY KEY(employeeId)
);

CREATE TABLE address(
    addressId   VARCHAR(30)
                NOT NULL,

    employeeId  INT,

    Address1    VARCHAR(120)
                NOT NULL,

    Address2    VARCHAR(120),

    Address3    VARCHAR(120),

    City        VARCHAR(100)
                NOT NULL,

    State       CHAR(2)
                NOT NULL,

    Country     CHAR(2)
                NOT NULL,

    PostalCode  VARCHAR(16)
               NOT NULL,

    PRIMARY KEY(addressId),

    FOREIGN KEY(employeeId) REFERENCES employee(employeeId) ON DELETE SET NULL

);

ALTER TABLE employee
ADD FOREIGN KEY(addressId)
REFERENCES address(addressId)
ON DELETE SET NULL;

I would love to know if there are any other ways to create an appropriate relationship between employee and address without using GUID. Another way would be a specified (int) value, but in that case a disadvantage would be:

• Introducing by mistake a FK != PK which will lead to a poor relationship between the tables.

EDIT:

Some of you suggested in the comments to change "UUID" indexing to AUTO_INCREMENT but the problem for me arises when I have to insert employees from my WPF-application.

The PK addressId from address will keep increasing on its own. What am I supposed to pass into the FK then to keep the relationship tight and correct then?

Should I create a variable of type int, let's say var i = 0 and every time I insert one employee -> increase that variable by one -> assign it to the FK or?

Answer 1

You'll want something like this in order to do a many-to-many association between employees and addresses (Address types will be like home, work, billing, travel, etc.). You can then create other entity tables to track associations between those entities and addresses as well. In the table employee_address, those columns will be foreign keys back to their relative tables.

As for using GUIDs vs INT for primary keys, numeric values read faster than string values on a JOIN. Depending on how large your data gets, you may need to switch from INT to BIGINT years down the road.

Also, give these people some space to enter their names. 20 characters is way too short, especially for last names.

employee
--------
employee_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(255) NOT NULL, 
lastname VARCAHR(255) NOT NULL,
gender BIT NOT NULL,
birthdate DATE NOT NULL

address
---------
address_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
address1 VARCHAR(255),
address2 VARCHAR(255),
address3 VARCHAR(255),
city VARCHAR(255),
state CHAR(2),
country CHAR(2)

address_type
--------------
address_type_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
address_type VARCHAR(255)

employee_address
-----------------
employee_address_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
employee_id INT,
address_id INT,
address_type_id INT