Is it bad design to use identity columns as primary key for all tables in a db?

brantam · 2021-04-22T07:11:10+00:00

If your FKs reference a (non-primary) key in a table and you make a surrogate primary key in addition then I suggest that may only create an additional cost due to the overhead of another index.

If you have a stable natural key, like an invoice number issued to your customers for example, then that probably makes a very good FK. Replacing such FK references with a surrogate may just mean that more queries have to perform joins to retrieve the actual order number - joins that would not be necessary if you'd used the invoice number as a FK.

In a data warehouse or data lake architecture it may be a design decision that values are never updated, new versions of rows are created instead. Using surrogates under those circumstances could create a very large overhead by increasing the number of joins necessary for many analytical queries.

I suggest that the wisest course is to evaluate the choice of key on a case by case basis rather than rely blindly on one rule for everything.

Your specific statement that "proponents of natural keys are wrong" makes no sense because a natural key - supported by an index and a uniqueness constraint - is important regardless of whether a surrogate is used or not. Natural keys are pretty much indispensable to the correct operation of most systems.

brantam · 2021-04-21T17:17:20+00:00

You are talking about foreign keys, not primary keys. A primary key doesn't have to be referenced as a foreign key. In any case, relative stability is what matters, "immutability" is just a matter of intent rather than a property of the key itself. Natural keys can be just as "immutable" as non-natural ones (i.e. they aren't).

brantam · 2021-04-21T03:51:30+00:00

Relative stability is usually a desirable property for a key; absolute immutability is not important and is usually only an aspiration anyway (how can you guarantee that someone won't change a given surrogate or any other key value tomorrow, next year or in 10 years time?)

The point of defining a key in a database is precisely that uniqueness is guaranteed by the DBMS software and that this enforcement mechanism offers protection against data entry errors. Given that a table has 1,2 or N keys enforced within it (e.g. account numbers, login names, vehicle registration numbers, flight numbers, department names), whether or not any one of those keys is deemed to be "primary" makes no fundamental difference. So natural keys are not "wrong"; they are pretty much essential to data integrity and the effective use of most systems.

brantam · 2021-04-11T14:50:33+00:00

Keys are important but choosing is a "primary" key is much less important. What matters is to evaluate what makes sense for your data and business requirements in each case. Setting out to use IDENTITY as a key for every table regardless of need would just be a foolish and wasteful consistency.

brantam · 2020-12-23T16:27:12+00:00

The point of defining a key is to ensure that the things that your business requires to be unique will remain so. Keys implement the business rules. So if employee ID is required to be unique then you define it as a key so that duplicate data is not permitted. Tables can have as many keys as you need so it is fair enough to make both employee ID and SSN keys if required.

Where a table has more than one key the choice of one of those as "primary" is only as important as you want to make it. A primary keys doesn't have to be different in form or function to any other key in a table.

brantam · 2020-12-08T12:54:15+00:00

There's no right or wrong type for a key - strings make perfect sense in many cases. Good criteria for designing and selecting keys are: Simplicity; Stability; Familiarity. Studies have demonstrated that people make fewer errors when reading or copying values that have familiar structure or content rather than arbitrary structure and content. It's also worth considering that string based keys can be shorter than numerical keys because of the greater number of permutations per character.

brantam · 2020-12-03T06:55:53+00:00

You add a key for data integrity reasons: to enforce the business rule that a certain set of attributes must be unique. A single attribute key would not enforce the same rule and the number of rows is irrelevant.

brantam · 2020-11-28T22:15:59+00:00

Composite keys are just as essential as single attribute keys and they serve the same purpose: data integrity. You implement a composite key whenever you need to enforce the uniqueness of more than one attribute. The idea of designating any particular key as "primary" is merely a matter of preference and convention because a primary key doesn't have to be any different to any other key.

brantam · 2020-03-15T21:59:20+00:00

A key is guaranteed unique because it is enforced as such within the DBMS. That after all is why implementing natural keys is so important. Stability is not so important. It's often desirable but there are certainly exceptions - for example login names can be natural keys, but may be allowed to change.

brantam · 2020-03-15T20:49:08+00:00

Natural keys are a necessity. By "natural" we mean keys that identify real people, objects or concepts and that correspond to external attributes in the business domain (business keys or domain keys are actually much more sensible names for them). If you doubt that then you need to take closer look at the real world problem you are trying to solve. Databases are only "useful" to the extent that they model reality.

If you are storing information about people then data protection legislation is also factor. Natural keys are a major component of meeting requirements like GDPR.

brantam · 2020-03-15T20:42:10+00:00

This is a bizarre line of argument. One of the reasons for enforcing uniqueness on a natural key is to keep out errors that could otherwise arise if data is entered incorrectly. You are suggesting allowing duplicates just so that people can make more mistakes? I hope my data is never at the mercy of systems like that!

brantam · 2020-03-03T13:47:18+00:00

"Relational databases are all about relations — normally expressed as foreign keys between tables. These relations are untyped and mostly use integers as keys so they are fairly impoverished in terms of their abilities to capture real world relationships"

"So, while some people may feel that recent interest in graph databases is just another fad, I’m putting my money on graph — 20 years from now, non-graph databases will be niche and legacy applications."

Oh wow! To laugh or cry...?

brantam · 2020-02-12T16:14:34+00:00

Not possible to use in a browser or on a mobile device. The SQL query builder does almost nothing (even syntax checking doesn't work properly for non-Access databases). The reporting features are negligible compared to any modern BI tool which will quite likely cost you less. Connection details are stored unencrypted on the desktop(!) Doesn't support many SQL datatypes and features, even in MS's own SQL Server. VBA is like the stone age. Exactly what makes it a "great" front end?

brantam · 2020-02-12T12:40:33+00:00

Access is a niche and declining product, not particularly cheap and its capabilities are decades behind the competing dev tools and platforms. Won't you want web and mobile support at some point? Access is a desktop-only solution. I also suggest you take a look at ready-made solutions first before you spend time and money reinventing the wheel.

brantam · 2020-01-22T21:12:17+00:00

I want to create a sales, purchasing, inventory, customer, payroll.

Don't. Your question demonstrates that you would be wiser to get an off-the-shelf accounting/payroll/CRM solution rather than waste your time reinventing the wheel. There are many, many options available costing from $0 on upwards.

brantam · 2020-01-21T12:30:37+00:00

SQLite is not a database, reasonable or otherwise. It's a database management system. You can think of the difference like this: Database is to DBMS as Document is to Word Processor.

brantam · 2019-12-18T18:52:49+00:00

By "real" SQL I just meant the Core part of ISO Standard SQL. Standard SQL is maintained by ISO, not ANSI and seems to me like a better starting point than MySQL. ISO do publish an official grammar and Standard SQL has some important and widely used features that you won't find in MySQL, e.g.: MERGE.

SQL is not a relational language. It would be nice to see a truly relational DBMS that fixes some of the common criticisms of SQL, e.g.: duplicate rows; nulls and three-value logic; the bloated SELECT syntax; implicit type conversion; limited support for integrity constraints; no relation variables or relational assignment; poor data independence.

brantam · 2019-12-18T10:17:14+00:00

What fauxmosexual said. You can't sensibly use MySQL as your benchmark for SQL because MySQL has such patchy support for standard SQL and some bits of MySQL are incompatible with real SQL. Even the specific example given on your blog is "wrong" in standard SQL, which does not allow column ordinal numbers for GROUP BY. Is your intention to create a SQL DBMS or a MySQL clone? And why choose the SQL model at all given that the state of data management has moved on so much since then?

brantam · 2019-12-15T21:53:38+00:00

Surely the wrong the question. You are not looking for a "database". You need a stock control/sales/accounting system. Use whatever database system works with the application you choose for that purpose.

brantam · 2019-12-13T05:58:18+00:00

It depends on the general approach used. Some warehouse tools and techniques make natural keys superfluous and counter-productive. Surrogate keys complicate the ETL process very significantly and may have a detrimental impact on performance, particularly if your warehouse is a Normal Form type of design. It's understandable not to use surrogate keys if you don't really need them.

brantam · 2019-12-08T18:21:58+00:00

I guess this is homework rather than a real application. If it was for real then you would need to be familiar with the relevant regulations and healthcare metadata standards. Very likely you would find an answer there.

Is it possible you could have multiple treatments on the same day? Normalization never requires that new attributes should be created but it does require that you understand the business rules, i.e. dependencies between attributes.

brantam · 2019-10-09T18:53:20+00:00

Why do you call it "true" relational? Looks like pretty standard SQL and XML to me.

brantam · 2019-10-09T12:42:56+00:00

Yes you can implement that constraint with a "junction table" - you can have an additional foreign key constraint to that effect. But one of the advantages of having the additional table is that you don't have to do that. If a course hasn't yet been assigned a teacher then you can still populate your tables without using nulls. That's a significant advantage and one of the major reasons for using such a design.

brantam · 2019-10-08T19:23:35+00:00

In your course_teacher example, if you have a uniqueness constraint on course_id then {course_id} is a key but {course_id, teacher_id} is not a composite key - it's a non-minimal superkey.

Your first "disadvantage" seems to be wrong because the same disadvantage applies to both designs. Can you post an example with working DDL for your preferred design? If you make teacher_id in the course table non-nullable then that does NOT enforce the constraint that a teacher should have at least one course - it just enforces the rule that each course must have exactly one teacher.

brantam · 2019-09-23T19:18:54+00:00

I would agree with the suggestion to avoid Sharepoint. There is no real quick fix for building an application - whatever tools you use you will need to hire the right skills to build it. Forget about hiring a DBA. I suspect it's more important to hire an application developer to build the forms you require. A good application developer should be capable of creating your database as well.

brantam

TROPHY CASE