Surrogate key is a much better option in this case. Your problem domain), therefore introducing a "easy" natural key because you would need to use all of the columns of theĪddress table to form a key for itself (you might be able to getĪway with just the combination of Street and ZipCode depending on Introduce a new column, called a surrogate key, There are two strategies for assigning keys to tables:Ī natural key is one or more existing dataĪttributes that are unique to the business concept.įor the Customer table there was two candidate keys, in this case Comparing Natural and Surrogate Key Strategies Figure 1.Ī simple PDM modeling Customer and Address.Ģ. Table to maintain the relationship with rows of Address. Similarly, the AddressID column is part of the primary key of CustomerHasAddress as well as a foreign key to the Address Interesting thing about the CustomerNumberĬolumn is the fact that it is part of the primary key for CustomerHasAddress as well as the foreign key to the Customer Has a composite primary key, the combination of CustomerNumber and AddressID.Ī foreign key is one or more attributes in an entity type that representsĪ key, either primary or secondary, in another entity type.įoreign keys are used to maintain relationships between rows.įor example, the relationships between rows in the CustomerHasAddress table and the Customer table is maintained by the CustomerNumber column within the CustomerHasAddress table. Information if it has the person's social security number. #Postgresql appily 2 primary keys softwarePerson's customer number although your software can get at the same That the preferred way to access customer information is through the value of a Model (PDM) for a physical address using the That represents a key, either primary or secondary, in another entity type. Also known as a secondary key, isĪnother unique identifier of a row within a table. Perhaps not even a key at all within a physical data model. Keys are called candidate keys because they are candidates to be chosen as the primary Of name and phone number (assuming the combination is unique) is potentially a SSN is one candidate key for the Person entity type and the combination For example, if we only interact with American citizens then (note: some people don't believe in identifying candidate keys in LDMs, so Keys, also referred to simply as unique identifiers An entity type in a logical data model will have zero or more Person entity (assuming the scope of your organization is limited to the U.S.). SSN could be used as a natural key, assuming privacy laws allow it, for a Isn't guaranteed to be true, but it's pretty darn close in practice). citizens are issued a Social Security Number (SSN) A key that is formed of attributesĮxample, U.S. Identifies a row within a relational table. Key would be formed of one or more table columns whose value(s) uniquely Key is one or more data attributes that uniquely identify an entity. Let's start by describing some common terminology pertaining to To know what you're doing if you want to get it right. Surrogate keys each have their advantages and disadvantages, and that no Typically they're doing little more than sharing the prejudices of their These people invariably prove to be wrong, Use natural keys and others will tell you that you should always use Some people will tell you that you should always In particular, itįocuses on the issue of when to use natural keys and when to use Keys to a table within a relational database. This article overviews strategies for assigning primary Choosing a Primary Key: Natural or Surrogate?
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |