Preparing to expand

[lukeurtnowski]

I have a mysql table named properties.
I want to prepare for the future to allow different property locations
So, im thinking I should add a location table
and add a location_id, row to the properties table,

create a locations table like

CREATE TABLE locations (
id TINYINT,
PRIMARY KEY(id),
name VARCHAR(15)
);

add 2 locations

INSERT INTO locations (id,name) VALUES (0,‘Shores’), (1,‘Village’);

Add location_id to the properties table (and make it a FK

CREATE TABLE properties(
id SMALLINT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
…
…
location_id TINYINT,
…
…
FOREIGN KEY (location_id) REFERENCES locations(id)
)

Is that a good setup if I want to add more locations later?

[r937]

yes – provided you continue to assign location id values manually

if you choose to use an auto_increment for it, then of course the value 0 is not allowed