>> I'm building a web app to maintain some data, which is mostly flat but will benefit from having some static look-up tables. Typically these look-up tables with have at most a dozen rows and are unlikely to change over time. <<
There is a really good chance you can put them into CHECK() constraints to protect your data integrity
>> Consider the two different DDL scripts. The first creates a look-up table CustomerType with an IDENTITY primary key, and the Customer table then has a Foreign Key of the CustomerType's Identity column: <<
This is totally wrong. The identity property is highly proprietary (jibber work with the original Sybase SQL Server?) And accounts physical insertions to one table in one place on one hard disk blah blah blah. What's really stupid is to see people throw in a column called "id" not understanding that it has to be "<something in particular>_id" according to the law of identity. This is the very foundation of all Western logic.
Since a table models a set, its name has to be either a collective noun ("Personnel" and not "Employees"), or if there is no collective noun, then a plural. If the table has a singular name, then it should be one and only one entity in that set. If you want to follow good practices, ANSI and ISO standards and make some kind of sense of things, then your 1st example of customers (I hope you really do have more than one in spite of what you said) it would look more like this. I picked a fixed length character string the size of a credit card number is the identifier. Non-RDBMS people think they can simply sequentially numbers something. But we believe in keys, validation and verification. There's also no such crap as a "<something>_type_id" ; those post fixes are what ISO calls attribute properties and an attribute could have only 1 of them. I'm going to guess that this is a customer type. Because the type is on a nominal scale, it can't be a numeric. I'm also going to assume that you feel no obligation to follow the international postal Union rules about the length of address lines on envelopes, so you can go ahead and use 50 characters instead of the standard 35. That standard number comes from 3 1/2 inch labels used by the postal Union for forwarding mail and every country is supposed to be able to knock an address line down to that length.
CREATE TABLE Customers
(cust_id CHAR(16) NOT NULL PRIMARY KEY,,
customer_name NVARCHAR(50) NOT NULL,
cust_type CHAR(3) NOT NULL
CHECK(cust_type IN (..)),
Since customer types are probably a relatively small domain (less than 100 codes), it's better to put them in the check clause then to give them their own table. If they were volatile, or a lot of them, then you would consider putting them their own table and using a references clause to get to them.
>> (I wondered if anyone had any strong feelings one way or another about these approaches? <<
I always follow ANSI/ISO standards, the metadata committee standards, and basic data modeling principles.
>> At my last place (a month ago) many devs (we are all full-stack .NET devs) chose not to enforce data integrity in the database, but in the code that surfaced the data. I'm old school and prefer to enforce relational integrity, but I'm wondering whether a relatively meaningless Id IDENTITY PK is nowadays deprecated. <
Quick, give me the address of the person in charge of this project! It's going to fall on its ass in 6 months and I can come in and charge them 4-5 figures the day for consulting! This is how I earn my lifting; patching up stupid stuff like this.
Also, the use of the physical insertion row count called IDENTITY as a primary key and a table has earned me tens of thousands of dollars over the years. Eventually, it doesn't work, and I come in and find the real keys in the table to save the day.. It's really fun they're trying to move from SQL Server to another database, like DB2 or Oracle.
Please post DDL and follow ANSI/ISO standards when asking for help.