ENUM vs Check Constraint vs Lookup Table: Which Is Better?
Vložit
- čas přidán 21. 07. 2024
- 📝 Bonus PDF: This ERD, descriptions of each table and column with sample data, and SQL scripts to create all of the tables on your own database: www.databasestar.com/dbdesign...
🎓 Learn how to design an effective database and create it using SQL: databasestar.mykajabi.com/edd...
There are a few ways you can restrict the values in a particular column.
In this video, I'll demonstrate the different ways you can do this, the advantages of each, and which method I recommend.
You'll learn about the ENUM concept in SQL, using a check constraint, and using a lookup table.
⏱ TIMESTAMPS:
00:00 - Our scenario
00:54 - Option 1: ENUM
02:43 - Option 2: Check Constraint
04:55 - Option 3: Lookup Table
🔗 VIDEO LINKS:
Scripts from this video: github.com/bbrumm/databasesta... - Věda a technologie
I was always tempted to use ENUMs, I didn't know about CHECK constraints and I always thought LOOKUP TABLES were overkill in resources and performance, thanks for finally clearing it out for me.
Glad I could help!
i was designinf an RBAC for my system and came up with this tutorial.i was about to use enums role types but just one statement from why use lookup table changed my idea completely.Thank you
Thanks for sharing, glad to hear it helped you with your current work.
In addition to easier to maintain, using "lookup table" also will be easier to filter based on the certain business-logic from application side. For example, we can display only some specific options which have "active" status in add form, but display all options in edit form and list and view pages.
That's a good point - yes it is another benefit!
Really like lookup and use it in many of my project
Glad to hear it helps!
can you please do a tutorial on uuid and big int.when to use one over the other on what usecases and is it safe to use uuid as primary keys.
Good idea! I can create a video on this.
Lookup table is simple and easy to understand
That’s right, it is