MySQL homeowner database

Question

This database must be able to track multiple home owners, who may own many properties, have many phone numbers, and their mailing address may depend on what month we're in. Each property may have more than one owner.

Please point out all bad coding practices.

create table home_owner
(
    owner_id        SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name      VARCHAR(10),
    last_name       VARCHAR(20)
);

create table email
(
    email_id        SMALLINT UNSIGNED PRIMARY KEY,
    email_address   VARCHAR(40)
);

create table owner_email
(
    owner_id        SMALLINT UNSIGNED,
    email_id        SMALLINT UNSIGNED,
    PRIMARY KEY emails_PK(owner_id, email_id),
    CONSTRAINT owner_email_FK FOREIGN KEY(owner_id) REFERENCES    home_owner(owner_id) ON DELETE CASCADE,
    CONSTRAINT email_owner_FK FOREIGN KEY(email_id) REFERENCES email(email_id) ON DELETE CASCADE
);

create table phone_number
(
    number_id       SMALLINT UNSIGNED PRIMARY KEY,
    phone_number    VARCHAR(10)
);

create table owner_number
(
    owner_id        SMALLINT UNSIGNED,
    number_id       SMALLINT UNSIGNED,
    PRIMARY KEY numbers_PK(owner_id, number_id),
    CONSTRAINT owner_number_FK FOREIGN KEY(owner_id) REFERENCES         home_owner(owner_id) ON DELETE CASCADE,
    CONSTRAINT number_owner_FK FOREIGN KEY(number_id) REFERENCES phone_number(number_id) ON DELETE CASCADE
);

create table seasonal_address
(
    seasonal_id     SMALLINT UNSIGNED PRIMARY KEY,
    address         VARCHAR(50),
    months          VARCHAR(27)
);

create table contact_info
(
    owner_id        SMALLINT UNSIGNED,
    seasonal_id     SMALLINT UNSIGNED,
    PRIMARY KEY contact_PK(owner_id, seasonal_id),
    CONSTRAINT owner_info_FK FOREIGN KEY(owner_id) REFERENCES home_owner(owner_id) ON DELETE CASCADE,
    CONSTRAINT seasonal_info_FK FOREIGN KEY(seasonal_id) REFERENCES seasonal_address(seasonal_id) ON DELETE CASCADE
);

create table property
(
    property_id     SMALLINT UNSIGNED PRIMARY KEY,
    address         VARCHAR(50),
    lot_number      SMALLINT UNSIGNED
);

create table owner_property
(
    owner_id        SMALLINT UNSIGNED,
    property_id     SMALLINT UNSIGNED,
    PRIMARY KEY properties_PK(owner_id, property_id),
    CONSTRAINT owner_property_FK FOREIGN KEY(owner_id) REFERENCES home_owner(owner_id) ON DELETE CASCADE,
    CONSTRAINT property_owner_FK FOREIGN KEY(property_id) REFERENCES property(property_id) ON DELETE CASCADE
);

insert into home_owner (first_name, last_name) values
("Angel", "Flop"),
("Bob", "Hoe"),
("Sue", "Hoe");

insert into email values
(1, "[email protected]"),
(2, "[email protected]"),
(3, "[email protected]");

insert into property values
(1, "123 Rainey", 123),
(2, "234 Bob", 1298),
(3, "697 Kolp", 782);

insert into seasonal_address values
(1, "7667 Noob", "1,2,3"),
(2, "2383 Fob", "4,5,6,7,8,9,10,11,12"),
(3, "7823 Flower", "1,2,3,4,5,6,7,8,9,10,11,12");

insert into phone_number values
(1, "5203601083"),
(2, "8039023093"),
(3, "2387784334"),
(4, "2377823782");

insert into owner_number values
(1, 1),
(1, 2),
(2, 3),
(3, 4);

insert into contact_info values
(1, 1),
(1, 2),
(2, 3),
(3, 3);

insert into owner_property values
(1, 1),
(1, 2),
(2, 3),
(3, 3);

insert into owner_email values
(1, 1),
(2, 2),
(3, 3);

select af.property_id 
from property af
inner join owner_property op
on af.property_id = op.property_id
where op.owner_id = 1; //find all properties of owner_id = 1

select a.*
from home_owner a
inner join owner_property op
on a.owner_id = op.owner_id
where op.property_id = 3; //find all owners of property_id = 3

Table definitions here.

Answer 1

For the table definitions, just use an INT for the keys. You probably aren't saving anything by using SMALLINTs. Schema changes tend to be a pain in larger projects, so you might as well try to avoid any possibility of trouble.

CREATE UNIQUE INDEX index_PK ON owner_property(property_id, owner_id); is redundant, since the table already has PRIMARY KEY properties_PK(owner_id, property_id).

The foreign key constraints look well done.

Having separate tables just for email and phone_number is probably overkill. The e-mail addresses and phone numbers could just be attributes on the owner_email and owner_number tables, respectively. I would put NOT NULL constraints on both of those attributes. The phone_number field should probably be longer than VARCHAR(10). (Note that MySQL tends to silently discard the part of the string beyond the length limit.)

Similarly, VARCHAR(50) for the property address is probably too stingy.

---

For your first query…

select af.property_id 
from property af
inner join owner_property op
on af.property_id = op.property_id
where op.owner_id = 1; //find all properties of owner_id = 1

… you don't need to join anything at all.

select property_id 
    from owner_property op
    where owner_id = 1;

---

For your second query…

select a.*
from home_owner a
inner join owner_property op
on a.owner_id = op.owner_id
where op.property_id = 3; //find all owners of property_id = 3

• Use better indentation. SELECT has a FROM clause and a WHERE clause. FROM has an INNER JOIN. INNER JOIN has an ON.
• Use a table alias that makes better sense (or don't use an alias at all).

select o.*
    from home_owner o
        inner join owner_property op
            on o.owner_id = op.owner_id
    where op.property_id = 3; //find all owners of property_id = 3