In the realm of Information Technology, or IT as it's more commonly known, an enum is a special data type that encapsulates a set of predefined constants. As such, the variable may only hold one of the values that have been predefined for it. Common examples include compass directions of NORTH, SOUTH, EAST, and WEST or the days of the week.
One of the complicating factors when storing enums in a database table is that their values may be numeric or alphabetic (i.e. strings). Moreover, you'll want to prevent users from adding any values to the table that are not part of the permissible enum set. We'll be addressing both of these issues in today's blog.
Strings as Primary Keys
In this third and final installment of this series on choosing a Primary Key for relational databases we'll be examining some of the reasons for employing string data as a Primary Key (PK). Recall that, in Part 1, we covered Natural and Surrogate Primary Keys and considered why one might choose one over the other. Part 2 explored String and Numeric data types as Primary Keys in an effort to ascertain whether one is preferable to the other. Now it's time to set the record straight and conclude whether or not string - or alphabetic - data can make a suitable PK.
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.
- 2024 (1)
- 2023 (1)
- 2022 (1)
- 2021 (1)
- 2020 (1)
- 2019 (1)
- 2018 (1)
- 2017 (1)
