How To Clean People's Names In Databases
How to Design a Clean Database
18 all-time practices to continue names unproblematic and consistent
No thing what kind of developer y'all are, every one time in a while, nosotros come across an API which returns data in such a manner that nosotros don't have to spend much time understanding it.
Only generating this blazon of clean and consistent effect takes fourth dimension, endeavour, and experience. Today we will accept the offset pace towards designing a clean database.
Nosotros are keeping it short and to the point. Let'southward start
Some Terminology
Table: this is a collection of data
Primary Key: This is the unique identifier of a table
Attribute: means property of your data. For example, name is an attribute of a user .
Data Type: Data types represent the various types of your data. For case -string, int, timestamp, etc.
1. Words should be underscore separated
When your attribute name has more than 1 discussion, so split up it with snake_case. Don't apply camelCase or any other example for consistency.
bad
wordcount or wordCount Good
word_count Reason
- Improves readability
- Names can become more platform-independent
ii. Data Types Should not Be Names
Never accept information-types as your cavalcade proper noun. This happens by and large for timestamp parameters. Give a meaningful proper noun to information technology.
Bad
timestamp or text Proficient
created_at or description Reason
- Using data types can create confusion on the other terminate of the awarding.
- Giving a proper name gives more context to the usage of the parameter.
3. Attribute names should exist lowercase
Don't apply upper-case names for your attributes.
Bad
Description Proficient
description Reason
- This practice avoids confusion from upper-instance SQL keywords
- Information technology can ameliorate typing speed
four. Write Full Words
Don't attempt to shorten the names of columns for the sake of space or whatever other logic. Try to exist as explicit as possible.
Bad
mid_name Skilful
middle_name Reason
This dominion promotes self-documenting design
5. But utilise common abbreviations
An exception of dominion-4 is when you lot accept a widespread abbreviation. In those situations, get for the short ane.
Skillful
i18n But if you discover yourself in confusion, go for the full name. Information technology'southward an investment you lot are making for the hereafter.
6. Avoid having numbers in a column name
Believe it or not, I accept seen it enough. Never accept numbers in your cavalcade proper noun.
Bad
address1 , address2 Expert
primary_address, secondary_address Reason
This is a sign of very poor normalization on your end. So try to avert it as much equally possible.
vii. Utilize brusque table names
Exist very careful when naming tables because long table names can have a huge bad impact in the time to come.
Bad
site_detail Practiced
site Reason
Short table names will help you when yous create relational columns and linking tables.
viii. Watch for reserved words
Each database has some reserved words. Larn them and avert them.
Bad
user lock table etc Listing of reserved words for some popular database
- Postgres
- MySQL
- Oracle
9. Atypical names for tables
Ever try to apply singular names for tables. This is a controversial 1, and unlike people have unlike opinions. But stick to one.
bad
users and orders Expert
user and order Reason
- This promotes consistency with primary keys and lookup tables
- Pluralization can be tricky sometimes. Then having singular table names tin can brand it easier to program.
10. Linking tables should have alphabetical order
When creating a junction table, concatenate the names of the 2 tables in alphabetical order.
Bad
book_author Good
author_book xi. Singular Cavalcade Names
Usually, it's the best do unless you are breaking information normalization rules.
Bad
books Good
book 12. Chief primal proper name
If information technology's a single column, then it should be named every bit id
CREATE Table lodge (
id bigint PRIMARY Fundamental,
order_date date NOT Zero
); 13. Foreign central proper name
It should exist the name of the other table and the referred field. For instance, if you are referencing a person inside your team_member the table and then you can do information technology like this.
CREATE Tabular array team_member (
person_id bigint Non NULL REFERENCES person(id),
); 14. Never suffix cavalcade names with types
There is no signal in suffixing your column names with types of data. Avoid doing this.
Bad
name_tx Skilful
name 15. Indexes should have both table and column name
If y'all are creating an alphabetize, then have the table name followed by the column names that yous are referencing
CREATE TABLE person (
id bigserial PRIMARY KEY,
first_name text NOT NULL,
last_name text Non Zero,
); CREATE INDEX person_ix_first_name_last_name ON person (first_name, last_name);
sixteen. Engagement type column names
Suffix your appointment-type column names with _on or _date .
For example, if you have a column for storing the updated date, then do this,
Skillful
updated_on or updated_date 17. Date-Time type cavalcade names
If your column name has time with it, so suffix them with _at or _time .
For example, if you lot want to store the guild time, and so
Bad
ordered Expert
ordered_at or order_time 18. Boolean type column Names
If you lot have boolean type column names, then prefix them with is_ or has_ .
Proficient
is_admin or has_membership Final Words
If you are already working on a project, stick to the convention that the project is already following. Considering
Merely matter worse than a bad convention is multiple conventions
But if you are learning or designing a database from scratch, having these rules in mind will take yous a long way.
What are your thoughts? Is there any rule yous disagree with? I am more happy to have some productive conversation in the comment department!
Have a great 24-hour interval! :D
Take something to say? Go in bear upon with me via LinkedIn
References
- https://launchbylunch.com/posts/2014/February/16/sql-naming-conventions/
- https://justinsomnia.org/2003/04/essential-database-naming-conventions-and-style/
Source: https://levelup.gitconnected.com/how-to-design-a-clean-database-2c7158114e2f
Posted by: ellisbelve1990.blogspot.com
0 Response to "How To Clean People's Names In Databases"
Post a Comment