SQL LOOKUP TABLE

Continuing my SQL learning, I’ve decided to complicate things a bit. In reloading, primers can be generally described as Large Rifle, Large Pistol, Small Rifle, or Small Pistol. So I’ve decided that on the Primer’s table, I’ll restrict the PrimerType field to those four options.

The way I’m going about this is with a Lookup Table. (My experience with another application that uses SQL extensively makes me think of the term “Validation Set.”)

I created the table:

CREATE TABLE LookupTable (
	LookupID INT PRIMARY KEY IDENTITY(1,1),
	TableContext VARCHAR(50) NOT NULL,
	LookupValue VARCHAR(50) NOT NULL, 
);

At about that time, I realized this design might keep me from looking up two fields on one table. So I need to modify the table to add another field.

ALTER TABLE LookupTable
ADD FieldContext VARCHAR(50) NOT Null;

Now populating the data for the Primer table in the Lookup Table:

INSERT INTO LookupTable (TableContext, FieldContext, LookupValue)
VALUES
	('Primer', 'PrimerType', 'Small Rifle'),
	('Primer', 'PrimerType', 'Large Rifle'),
	('Primer', 'PrimerType', 'Small Pistol'),
	('Primer', 'PrimerType', 'Large Pistol')
;

One thing I noticed as I was doing this was that the colors were not consistent across my value rows. I copied and pasted the first one, and made changes. It looked like this:

I thought the alternation was interesting, but it took me a minute to locate the cause. There was no ‘ at the end of the LookupValue in each row.

Now with that fixed and the data in the table, I created the Primer table. I needed help from ChatGPT to figure out how to do the FOREIGN KEY:

PrimerTypeID INT, 
FOREIGN KEY (PrimerTypeID) REFERENCES LookupTable(LookupID)

If I understand this correctly, it’s saying “This table has a field called PrimerTypeID, it’s an integer, and it will match the LookupID on the LookupTable.?

I realize doing this means I’m going to have to do some joins later, but again that’s the point of doing this. I was already planning on making views for the actual load data.

And now a realization… I don’t actually care about the manufacturer of the primer. Which means, I don’t need the Primer table at all. I can just reference the data on the lookup table itself, and in the end that’s probably a better use for that table; a place to put data that doesn’t need it’s own table. And now I’m wondering if I needed the Bullet or Powder tables. Or even the Caliber table.

I think the only way to be sure which is the “best” way while I’m learning is to do both. I’ll keep this DB as I’ve designed it now, but I’ll make another soon that uses a single lookup table for the common underlying data. (That’ was ChatGPT’s recommendation too.)