How To Clean People's Names In Databases

How to Design a Clean Database

18 all-time practices to continue names unproblematic and consistent

Photograph by Lukas from Pexels

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

Share this post