Timeline for Best Practice regarding table schema
Current License: CC BY-SA 3.0
15 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Apr 13, 2017 at 12:45 | history | edited | CommunityBot | replaced http://softwareengineering.stackexchange.com/ with https://softwareengineering.stackexchange.com/ | |
| Nov 17, 2016 at 17:54 | comment | added | Walfrat | Furthermore it's only declarative SQL line in the database instead of checking manually in each application that use the said database. | |
| Nov 17, 2016 at 15:44 | comment | added | Arseni Mourzenko | @Shaharyar: so your approach is to ignore referential integrity. This might be a valid choice in some cases, but it's usually not the case. Types, foreign keys, unique constraints, other constraints stored procedures with validation phase, etc. are here for a reason, because in most cases, we don't want random apps to write random data to the database. So no, validation of data is not exclusively done in code. | |
| Nov 17, 2016 at 15:39 | comment | added | Walfrat | I'll try, I will focus on the application side as an addendum to the database side. This solution can only be optimal if the design of the application that use it match to it, which is not simple as it seems. OP talk about best practices, but the best practices applied on a database only can be suboptimal if the application layer and the frameworks involved (if there is one) can't handle it properly. | |
| Nov 17, 2016 at 15:31 | comment | added | Arseni Mourzenko | @Walfrat: would you mind writing your own answer? Right now, I edited mine based on your comments, but I feel like stealing your idea. | |
| Nov 17, 2016 at 15:30 | history | edited | Arseni Mourzenko | CC BY-SA 3.0 | added 1402 characters in body |
| Nov 17, 2016 at 13:40 | comment | added | Shaharyar | Your answer is more about validation of data (which is the part of code) rather than the database design. Why IDs will be out of sync? Only if developer hasn't handled every thing properly. But combining will be a better approach I guess because data fetching will much more easy (off course proper indexing required) and in case of a new entity you won't need a brand new table. | |
| Nov 17, 2016 at 13:39 | comment | added | Walfrat | THe only problem with that approach, is that you get more complicated query if you want to query from the parent. For instance Hibernate using case switch and check which id from the join is not null to affect an identifier to identify the subclass. But if you only query from child table and only use the parent table only for reference, you're fine. | |
| Nov 17, 2016 at 13:37 | history | edited | Arseni Mourzenko | CC BY-SA 3.0 | added 65 characters in body |
| Nov 17, 2016 at 13:29 | comment | added | Arseni Mourzenko | @Walfrat: oh, I see now what you were talking about. | |
| Nov 17, 2016 at 13:28 | comment | added | Walfrat | @ArseniMourzenko this is exactly how inheritancy works with ORM. Child tables have a primary key which is also a foreign key which point to the parent table. Which mean when you need to create a new object, you need first to create the entry in the parent table, keep the id given (either from last_insert_id() or using a sequence and keep the value) and put it in the child table. This mean ther eis no duplicate id between child tables. | |
| Nov 17, 2016 at 13:25 | comment | added | Arseni Mourzenko | @Walfrat: this isn't something supported by commonly used RDMS such as Microsoft SQL Server or PostgreSQL, is it? Also, how would one know that the FK points to the user 123, and not a team or a group with the same ID? | |
| Nov 17, 2016 at 13:23 | comment | added | Faizan Hasan | @Walfrat no the parent tables are different | |
| Nov 17, 2016 at 13:20 | comment | added | Walfrat | Unless the tables User, Team, Group have a common "parent" table which their PK come from, inheritancy in database basically. But yes that's definitively a good point. | |
| Nov 17, 2016 at 13:18 | history | answered | Arseni Mourzenko | CC BY-SA 3.0 |