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. <\/p>\n\n\n\n
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.”) <\/p>\n\n\n\n
I created the table:<\/p>\n\n\n\n
CREATE TABLE LookupTable (\n\tLookupID INT PRIMARY KEY IDENTITY(1,1),\n\tTableContext VARCHAR(50) NOT NULL,\n\tLookupValue VARCHAR(50) NOT NULL, \n);<\/code><\/pre>\n\n\n\n
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.<\/p>\n\n\n\n
ALTER TABLE LookupTable\nADD FieldContext VARCHAR(50) NOT Null;<\/code><\/pre>\n\n\n\n
Now populating the data for the Primer table in the Lookup Table: <\/p>\n\n\n\n
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: <\/p>\n\n\n\n<\/figure>\n\n\n\n
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. <\/p>\n\n\n\n
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:<\/p>\n\n\n\n
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.?<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
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. <\/p>\n\n\n\n
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.) <\/p>\n\n\n\n
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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_mi_skip_tracking":false,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1905","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"aioseo_notices":[],"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false,"post-thumbnail":false,"newspaperly-grid":false,"newspaperly-slider":false,"newspaperly-small":false},"uagb_author_info":{"display_name":"admin","author_link":"https:\/\/pelnar.tech\/?author=1"},"uagb_comment_info":3,"uagb_excerpt":"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...","_links":{"self":[{"href":"https:\/\/pelnar.tech\/index.php?rest_route=\/wp\/v2\/posts\/1905","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pelnar.tech\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pelnar.tech\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pelnar.tech\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pelnar.tech\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1905"}],"version-history":[{"count":1,"href":"https:\/\/pelnar.tech\/index.php?rest_route=\/wp\/v2\/posts\/1905\/revisions"}],"predecessor-version":[{"id":1907,"href":"https:\/\/pelnar.tech\/index.php?rest_route=\/wp\/v2\/posts\/1905\/revisions\/1907"}],"wp:attachment":[{"href":"https:\/\/pelnar.tech\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1905"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pelnar.tech\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1905"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pelnar.tech\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1905"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"\"](\"https:\/\/pelnar.tech\/wp-content\/uploads\/2024\/09\/image.png\")
<\/figure>\n\n\n\n