String vs. Numeric Data Types as Primary Keys
Welcome back to this series on choosing a Primary Key for relational databases. In Part 1, we covered Natural and Surrogate Primary Keys and considered why one might choose one over the other. Today's instalment will explore String and Numeric data types as Primary Keys in an effort to ascertain whether one is preferable to the other.
Natural vs. Surrogate Keys
One of the first decisions you'll be faced with as a database designer is what kind of Primary Key (PK) to use on your tables. If you ask anyone who works with databases on a daily basis, whether database administrator, developer, or tester, you'll get a myriad of opinions and justifications to go along with them. Compounding the impediments to coming up with an answer is that there is no one size fits all solution. With that in mind, this series will present some reasons both for and against different types of PKs. Somewhere in all those ideas, there will be a few that will steer you towards the best type of PK to use for your organizational needs. In this first instalment, we'll compare the two basic types of PKs: Natural and Surrogate Keys. Later, we'll cover the questions of whether or not to use the database Auto Increment feature as well as which data type(s) - if any - make the best PKs.
Application developers have long held the belief that housing database operations within stored procedured yielded optimum performance and guarded against SQL Injection attacks. It was also thought that these advantages were worth the extra costs associated with maintenance, testing, and migration of database logic to a different vendor. In recent years, the tide has been turning away from stored procedures - or procs - towards Object-relational Mappers (ORM) such as Hibernate or Entity Framework as developers have begin to question these long-standing assumptions.
The Are Stored Procedures an Outdated Tool? article highlighted a few reasons for eschewing stored procedures in favor of application code and ORMs. This week, we'll explore the two myths introduced above and see if they still stand up to scrutiny today.
Stored procedures have been falling out of favour with some organizations for several years now. The preferred approach of these businesses for accessing their database(s) is to employ an Object-relational Mapper (ORM) such as NHibernate or Entity Framework. Over the next couple of blog articles, we'll explore their reasons for doing so, and whether this paradigm shift points to the eventual obsolescence of Stored Procedures.
Data Synchronization
You are now able to choose not to preview the results and deploy directly. Navicat now offers two buttons:
- Compare & Preview: preview the comparison results.
- Compare & Deploy: skip the preview and deploy immediately.
- 2024 (1)
- 2023 (1)
- 2022 (1)
- 2021 (1)
- 2020 (1)
- 2019 (1)
- 2018 (1)
- 2017 (1)
