> good database schema designs? Ones that ship.
Ones that last, I'd say.
Ask HN: What are some examples of good database schema designs?
51–60 of 181 posts
Re: Ask HN: What are some examples of good database schema designs?
#52On this topic: I'm in the process of making a compiler for a DSL I designed to help with the schema design process. https://gist.github.com/nomsolence/69bc0b5fe1ba943d82cd37fdb... Pictured is the compiler internals; the attached .syrup is the DSL. (It started as a tangent on a project I'm doing for my girlfriend, the schema described in the .syrup has been improved a bit since) Note: things after # are comments. I fi…
I really like the arrow for foreign keys. That and the intermediate table for n-n relationships (and the associate join queries) are the main pain point of SQL, in comparison to object modeling.
Re: Ask HN: What are some examples of good database schema designs?
#53Read up on third normal form. It’s all you need for 99% of oltp databases (the backend to a store) and read up on snowflake (The design not the company) and Star schema for datawarehouses for analytics.
Beware of crossing context boundaries when applying 3NF to commerce records.
E.g. The description of an item in a sales invoice is the description at the time the contract of sale is made, and must be immortalised as such as a copy of that description.
Re: Ask HN: What are some examples of good database schema designs?
#54> When I read about database schemas with thousands of tables I wonder what kind of problem could possibly require so many tables.
Businesses that trade in thousands of products, employ thousands of people in dozens of countries, with hundreds of different taxation, human relations, timekeeping, payroll, health insurance and retirement savings laws, dozens of sites, inventories in hundreds of locations managed by dozens of contractors, moved by one of potentially thousands of logistics firms with at least a dozen modes of shipment, reporting standards for multiple countries, reporting standards for stock exchanges, internal management reports, bank accounts in multiple countries, in multiple currencies, with a mix of transfers via a variety of means, hundreds of cash alternatives with varying rules about whether they are cash alternatives...
Modern large businesses are very, very complex.
Re: Ask HN: What are some examples of good database schema designs?
#551000 tables isn't an extraordinary amount. As far as database schema design goes it really depends on your needs, e.g. is your database intended for OLTP or OLAP use? Depending on your needs you can decide to what degree you want to normalise your tables, also there a lots schema designs to chose from (star, snowflake, etc.) and it's worth reading about them. Having a good schema design is about knowing the data you have and how you plan on using it, there is no one "good" solution in my opinion.
Edit: Security is also something worth taking into account, design for the applications and users that are going to access your database. You might create a separate schema for an application and separate schema for users and then grant them access to a "main" schema for instance, but this really all depends on your needs in the end.
Re: Ask HN: What are some examples of good database schema designs?
#56> good database schema designs? Ones that ship.
I have worked on some shot database designs that managed to ship.
Re: Ask HN: What are some examples of good database schema designs?
#57Read up on third normal form. It’s all you need for 99% of oltp databases (the backend to a store) and read up on snowflake (The design not the company) and Star schema for datawarehouses for analytics.
Beware of crossing context boundaries when applying 3NF to commerce records. E.g. The description of an item in a sales invoice is the description at the time the contract of sale is made, and must be immortalised as such as a copy of that description.
> E.g. The description of an item in a sales invoice is the description at the time the contract of sale is made, and must be immortalised as such as a copy of that description.
+1. I maintain a couple of systems that younger-me wrote 15+ years ago. If younger-me had understood this, I could have avoided much re-engineering :)
Re: Ask HN: What are some examples of good database schema designs?
#58Read up on third normal form. It’s all you need for 99% of oltp databases (the backend to a store) and read up on snowflake (The design not the company) and Star schema for datawarehouses for analytics.
Beware of crossing context boundaries when applying 3NF to commerce records. E.g. The description of an item in a sales invoice is the description at the time the contract of sale is made, and must be immortalised as such as a copy of that description.
As such, it helps to also study 4NF and 5/6NF (which are temporal normalisations). This allows us to refer a sale to an earlier 'version' of the product.
Re: Ask HN: What are some examples of good database schema designs?
#59The Kimball book series suggests some generalized schemas by industry, for the purpose of data warehousing. Here the academic schema rules tend to bend a bit - denormalization can become a useful and often necessary technique, for example.
Might be helpful for a rounded understanding of good schema design - it can depend on the context.
Re: Ask HN: What are some examples of good database schema designs?
#60PostGraphile [1] is a framework for generating a GraphQL API based on the tables in your database; as a result, good database design is crucial. Graphile Starter [2] is a quickstart project that demonstrates best practices for getting up and running with PostGraphile quickly. In particular, check out the SQL migration file in that project [3]. It demonstrates: 1. Dividing up tables so that one user can have more than…
Users having multiple addresses is something I've cursed a lot over. I work in a team that does data analytics for a news publishing company, and our print business is still very important. Unfortunately, in our database over print customers users are basically addresses because you don't really need to know how many people are receiving your paper as a distributor, only where and how many papers. Since it's also bee…
Multiple email support seems indeed complex, event the most popular CRM on the market doesn’t support this feature even if it’s requested a lot.
https://success.salesforce.com/ideaview?id=08730000000BrPIAA...