Within one Web application I am working on, all database operations are abstracted using some generic repositories defined over Entity Framework ORM.
However, in order to have a simple design for the generic repositories, all involved tables must define an unique integer (Int32
in C#, int
in SQL). Until now, this has been always the PK of the table and also the IDENTITY
.
Foreign keys are heavily used and they reference these integer columns. They are required for both consistency and for generating navigational properties by the ORM.
The application layer typically does the following operations:
- initial data load from table (*) -
SELECT * FROM table
- Update -
UPDATE table SET Col1 = Val1 WHERE Id = IdVal
- Delete -
DELETE FROM table WHERE Id = IdVal
- Insert -
INSERT INTO table (cols) VALUES (...)
Less frequent operations:
- Bulk insert -
BULK INSERT ... into table
followed (*) by all data load (to retrieve generated identifiers) - Bulk delete - this is a normal delete operation, but "bulky" from ORM's perspective:
DELETE FROM table where OtherThanIdCol = SomeValue
- Bulk update - this is a normal update operation, but "bulky" from ORM's perspective:
UPDATE table SET SomeCol = SomeVal WHERE OtherThanIdCol = OtherValue
*all small tables are cached at application level and almost all SELECTs
will not reach database. A typical pattern is initial load and lots of INSERT
s, UPDATE
s and DELETE
s.
Based on current application usage, there is very small chance of ever reaching 100M records in any of the tables.
Question: From a DBA's perspective, are there significant problems I can run into by having this table design limitation?
[EDIT]
After reading the answers (thanks for the great feedback) and referenced articles, I feel like I have to add more details:
Current application specifics - I did not mention about current web application, because I want to understand if the model can be reused for other applications as well. However, my particular case is an application that extracts lots of metadata from a DWH. Source data is quite messy (denormalized in a weird way, having some inconsistencies, no natural identifier in many cases etc.) and my app is generating clear separated entities. Also, many of the generated identifiers (
IDENTITY
) are displayed, so that the user can use them as business keys. This, besides a massive code refactoring, excludes usage of GUIDs."they should not be the only way to uniquely identify a row" (Aaron Bertrand♦) - that is a very good advice. All my tables also define an UNIQUE CONSTRAINT to ensure that business duplicates are not allowed.
Front-end app driven design vs. database driven design - design choice is caused by these factors
Entity Framework limitations - multiple columns PKs are allowed, but their values cannot be updated
Custom limitations - having a single integer key greatly simplifies data structures and non-SQL code. E.g.: all lists of values have an integer key and a displayed values. More important, it guarantees that any table marked for caching will be able to put into a
Unique int key -> value
map.
Complex select queries - this will almost never happen because all small (< 20-30K records) tables data is cached at application level. This makes life a little harder when writing application code (harder to write LINQ), but the database is hit much nicer:
List views - will generate no
SELECT
queries on load (everything is cached) or queries that look like this:SELECT allcolumns FROM BigTable WHERE filter1 IN (val1, val2) AND filter2 IN (val11, val12)
All other required values are fetched through cache lookups (O(1)), so no complex queries will be generated.
Edit views - will generate
SELECT
statements like this:SELECT allcolumns FROM BigTable WHERE PKId = value1
(all filters and values are int
s)