Preface
Recently I have developed a mobile application for the company I am working at. I am grateful to have an oppurtunity and precious experience to design the whole system from zero.
What I failed
-
Sparsity of Table. Some data looks identitcal, I have therefore tried to mix two interfaces into one table, for example:
where the type of this record depends on
SessionType
, which determines whether we useisDraftInstantIssue
orisDraftReply
. It causes some of the column being alwaysnull
(i.e., sparsity), and it causes confusion when do we use these booleans.From this lesson I should have made additional two tables named
InstantIssueDetail
andReplyDetail
,
and link these two tables to the original table via
InstantIssueDetail.session_id
andReplyDetail.session_id
respectively. The new tables group all thetype
-specific data.
-
Similar to the above case. A message can be of type
Image
,Voice
,Text
, and forturnately I can avoid the sparsity timely: -
Didn't Stop Bad Design at the Beginning (Data Duplication). In the past we had a tech lead designing a
UserRegistration
table (whereUser
table has been made already) which serves as an intermediate table beforeUser
.However 90% of data are the same. This results in unpredictable extra amount of works as every time we deal with registration we need to look at two tables.
If we want to select data, we need to
case, when, then, else, end
many times.
What I succeeded
Researched on various ORMs, youtube videos on those frameworks, pros and cons, combining the experience with sql tutorial from other languages, eventually
-
Adopted Prisma as a table migration tool in light of
Flyway
in spring boot andGoose
in golang. -
Used Kysely as a type-safe query builder.
-
Standardized the approach to version all the schema changes in database, and reproduce all the changes to different environments.
Our product works well with the database and developers can easily write their query with good semantic meaning, for example:
which an ORM cannot provide. Note that a table carries different meaning in differnent context!