Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ask HN: What are some examples of good database schema designs?

news.ycombinator.com

Post reply on HN
Print view View original

11–20 of 181 posts

Re: Ask HN: What are some examples of good database schema designs?

#11

Post by arethuza »

Namoshek wrote: post #3

I came to the same conclusion when I read that a basic SAP installation comes with over 20k tables. (see: https://retool.com/blog/erp-for-engineers/ )

A SAP partner once told me (the company I was working at was considering using SAP) that the deployment would have ~180K tables - I don't know if they got the figure wrong, I have misremembered (I did check when they said it) or maybe that's for a "fully loaded" instance.

Edit: Not SAP, but certain other ERP products have an alarming habit of not using foreign keys - which makes working out the structure of the database quite interesting...

Re: Ask HN: What are some examples of good database schema designs?

#12

Post by ganonm »

I'd highly recommend reading SQL Antipatterns. It's a very approachable book that illustrates how to design a database schema by taking commonly encountered scenarios and first showing you the naive approach. After explaining why this is bad, it then shows you the recommended way of doing it and why this way works better.

I think 'learning from how not to do something' is a really powerful pedagogical technique that should be more widespread.

Re: Ask HN: What are some examples of good database schema designs?

#13

Post by gberger »

arethuza wrote: post #11
Namoshek wrote: post #3

I came to the same conclusion when I read that a basic SAP installation comes with over 20k tables. (see: https://retool.com/blog/erp-for-engineers/ )

A SAP partner once told me (the company I was working at was considering using SAP) that the deployment would have ~180K tables - I don't know if they got the figure wrong, I have misremembered (I did check when they said it) or maybe that's for a "fully loaded" instance. Edit: Not SAP, but certain other ERP products have an alarming habit of not using foreign keys - which makes working out the structure of the datab…

How do you perform joins without foreign keys? Do you just have a column that is effectively a foreign key but is not marked as such?

Re: Ask HN: What are some examples of good database schema designs?

#14

Post by kohtatsu »

On 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 find even just defaulting to NOT NULL and not having to worry about commas is a boon for when I create schemas.

The DSL will of course support things like compound primary keys and SQLite's WITHOUT ROWID.

I'll post the code here, likely before the weekend: https://github.com/nomsolence/syrup

Re: Ask HN: What are some examples of good database schema designs?

#15

Post by deepaksurti »

coltnz wrote: post #10

I've been using Len Silverstein's Universal Data Models for 15 years. You'll be writing to lots of tables and will want views for your common aggregates. But you'll have the common tables you'll need, the patterns for those you don't and be able to handle new requirements with minimal change. There is no Customer table. "The Data Model Resource Book, Vol. 1: A Library of Universal Data Models for All Enterprises"

>> "The Data Model Resource Book, Vol. 1: A Library of Universal Data Models for All Enterprises"

[1] https://www.wiley.com/en-us/The+Data+Model+Resource+Book%2C+...

Re: Ask HN: What are some examples of good database schema designs?

#16

Post by GrumpyNl »

ganonm wrote: post #12

I'd highly recommend reading SQL Antipatterns. It's a very approachable book that illustrates how to design a database schema by taking commonly encountered scenarios and first showing you the naive approach. After explaining why this is bad, it then shows you the recommended way of doing it and why this way works better. I think 'learning from how not to do something' is a really powerful pedagogical technique that…

Free download can be found here http://www.r-5.org/files/books/computers/languages/sql/style...

Re: Ask HN: What are some examples of good database schema designs?

#17

Post by dizaime »

gberger wrote: post #13
arethuza wrote: post #11

Earlier quoted context omitted.

A SAP partner once told me (the company I was working at was considering using SAP) that the deployment would have ~180K tables - I don't know if they got the figure wrong, I have misremembered (I did check when they said it) or maybe that's for a "fully loaded" instance. Edit: Not SAP, but certain other ERP products have an alarming habit of not using foreign keys - which makes working out the structure of the datab…

How do you perform joins without foreign keys? Do you just have a column that is effectively a foreign key but is not marked as such?

Sure it is. We can perform DB joins with any column as long as the data type and the data value is matched.

Re: Ask HN: What are some examples of good database schema designs?

#18

Post by gerdusvz »

Schema's that reflects reality not the current specs. Flexibility is key. In my experience adding tables and migrating existing data to them is hard, adding columns easy. So spend extra time at the start on what tables there should be.

Spec: Product has a supplier [tables:product, supplier]

Reality: Product can be bought from multiple suppliers [table:product, supplier, product_supplier]

Re: Ask HN: What are some examples of good database schema designs?

#20

Post by Faaak »

mkettn wrote: post #9

10 years ago, I'd said "at least third normal form"... but today: Whatever gets the job done. When the application is not really dependent on weird queries (e.g. just a blog), screw the normal forms and design your schema to use the least number of queries for a certain task. Nobody understands three lines of code of queries with left and right joins. On the other hand, if your bookkeeping application uses a database…

I'd rather say: use an ORM ! It will design the DB schema better and faster than you. Still comprehensive enough
Post reply on HN