A
If I were you, I would include the new fields within the location table - while at the same time making considerable use of Declarative Referential Integrity.
I would do something like this (I haven't included all of your new fields - you can check out the fiddle here
Create and populate two tables, crime and weather:
CREATE TABLE crime (cr_rating VARCHAR (25) PRIMARY KEY);
INSERT INTO crime VALUES ('Low'), ('Medium'), ('High');
and
CREATE TABLE weather (weather_type VARCHAR (50) PRIMARY KEY);
INSERT INTO weather VALUES ('Scorching'), ('Sunny'), ('Mild'), ('Overcast'), ('Drizzle'), ('Rainy'), ('Stormy'), ('Hurricane');
Then create and populate a job table - see discussion below:
CREATE TABLE job (job_id INTEGER PRIMARY KEY, job_type VARCHAR (30) NOT NULL);
INSERT INTO job (job_type) VALUES ('Unknown'), ('Unemployed'), ('Blue collar'), ('White collar'), ('Manager'), ('Self-employed');
No need to use AUTOINCREMENT for reasons outlined here. Developers tend to compulsively use AUTOINCREMENT - possibly a hangover from MySQL standards and possibly some idea that it must somehow be "better" than the simpler code above.
Then create and populate the main location table:
CREATE TABLE the_location
(
loc_key INTEGER PRIMARY KEY,
loc_coords VARCHAR (50) NOT NULL, -- not sure how you store these in your system
loc_status INTEGER CHECK (loc_status IN (0,1)),
loc_rating INTEGER CHECK (loc_rating BETWEEN 1 AND 10),
loc_crime VARCHAR (25) NOT NULL,
-- could use a CHECK CONSTRAINT, but use a lookup table instead
-- note also that the types should match exactly
loc_weather VARCHAR (50) NOT NULL,
-- location_rating_tourism, -- same lookup table idea here (Good, Medium, Poor, Death-zone)
-- location_rating_income, -- list of social classes table
-- location_rating_jobs
CONSTRAINT ct_loc_crime FOREIGN KEY (loc_crime) REFERENCES crime (cr_rating),
CONSTRAINT ct_loc_weather FOREIGN KEY (loc_weather) REFERENCES weather (weather_type)
);
INSERT INTO the_location
(loc_coords, loc_status, loc_rating, loc_crime, loc_weather)
VALUES
('coord_1', 0, 7, 'Medium', 'Rainy'),
('coord_2', 1, 7, 'High', 'Mild');
I've left some of the other fields out, but if there is one and only one of these things associated with your location, then you can simply have them as a single field in the location table as discussed above.
However, consider the job table - corresponding to the location_rating_jobs record in your location table. Suppose there is a certain percentage of Unknown job_types, a certain percentage of Unemployed &c... per location?
In this case, you can use an Associative Entity (otherwise known as a joining or linking) table to cope with such a relationship. I would do something like the following:
CREATE TABLE location_job
(
lj_loc INTEGER NOT NULL,
lj_job INTEGER NOT NULL,
lj_percent INTEGER DEFAULT 0 NOT NULL,
PRIMARY KEY (lj_loc, lj_job),
CONSTRAINT ct_lj_loc FOREIGN KEY (lj_loc) REFERENCES the_location (loc_key),
CONSTRAINT ct_lj_job FOREIGN KEY (lj_job) REFERENCES job(job_id)
);
and then populate it respecting the FOREIGN KEY constraints.
INSERT INTO location_job
VALUES
(1, 1, 10), (1, 2, 10), (1, 3, 10), (1, 4, 10), (1, 5, 10), (1, 6, 50),
(2, 1, 30), (2, 2, 30), (2, 3, 10), (2, 4, 10), (2, 5, 10), (2, 6, 10);
And then, in order to find out the percentage of job_type by area, I would run the following query:
SELECT
tl.loc_key, tl.loc_coords,
lj.lj_loc, lj.lj_job, lj.lj_percent,
j.job_id, j.job_type
FROM the_location tl
JOIN location_job lj ON tl.loc_key = lj.lj_loc
JOIN job j ON lj.lj_job = j.job_id
ORDER BY tl.loc_key, j.job_id;
Result:
loc_key loc_coords lj_loc lj_job lj_percent job_id job_type
1 coord_1 1 1 10 1 Unknown
1 coord_1 1 2 10 2 Unemployed
1 coord_1 1 3 10 3 Blue collar
..
.. results snipped for brevity - see fiddle
..
Obviously, in a real query, the duplication of values would be eliminated and you would only have one of each necessary one - I've kept them all here so that you can effectively see the process of joining across tables.
The only area that falls outside the area of standard (current) SQL is the fact that the percentages per area cannot be > 100 (*) (Firebird can have queries in CHECK constraints - but it's about the only server that I know of which can). This is known as ASSERTIONs or "Declarative multi-row constraints" which have not yet been implemented by the major vendors - you'd have to use TRIGGERs for this - or maybe C code in SQLite.
(*) or perhaps your model would allow for those who were both Self-employed and Managers?
If this doesn't answer your question, please let me know. p.s. welcome to the forum!
