26 ms·

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

437 points by vanilla-almond 7y ago

I've seen many examples of database schemas but don't always recognise a good design over a mediocre or poor design. This is what I mean by schema (is this a good design?) https://www.drupal.org/node/1785994 https://www.drupal.org/node/1785994 When I read about database schemas with thousands of tables I wonder what kind of problem could possibly require so many tables. What are good schema examples? And what are some poor examples?

felixyz 7y ago
The Sakila schema: https://dev.mysql.com/doc/sakila/en/ https://dev.mysql.com/doc/sakila/en/ Which I learned about from the jOOQ folks: https://www.jooq.org/sakila https://www.jooq.org/sakila
davismwfl 7y ago
Kinda tough to give a good answer without more context, IMO. What I mean is that a good e-commerce schema that serves a single small store and runs off a single database server would look quite different then a multi-tenant or distributed data store for a e-commerce site at scale. The one you linked is a pretty typical relational model and isn't bad, but it has trade offs that I'd personally not make, however, that doesn't make it bad. In the end context, scale and usage all determine a good schema design. Sometimes what would be a good relational design on paper would be tragically horrid in practice once you get beyond a small dataset.
- billfruit 7y ago
  Yes, but where can we all see the schemas that has worked well and the ones which hasn't, I mean all that information isn't accessible to someone who wants to know the state of the art. In someways software industry has been really bad in documenting and sharing knowledge, it is really hard to get hold of documents of large software projects, and informed commentary on them from the industry for educational purposes, I wish someone would do something about it.
  - davismwfl 7y ago
    Very fair and very true, and I am 100% in agreement with your points, I do not know of a single resource we can go to as an educational toolset that is based on real world products/experience. The problem is the industry as a whole is locked into these NDA type agreements (along with proprietary information) which prevent a lot of the complete schema sharing etc which would help people learn the best. That said, some of the best things to look at are open source projects and research their pain points and highlights. That will many times point you to the tradeoffs they made in modeling and in code. For e-commerce things like magento and other open source solutions can be really informative, even woocommerce and how it shoehorns itself into wordpress can be informative as to the tradeoffs on highly normalized vs lightly normalized vs key/values etc.
Namoshek 7y ago
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/ https://retool.com/blog/erp-for-engineers/)
- arethuza 7y ago
  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...
  - gberger 7y ago
    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?
    - dizaime 7y ago
      Sure it is. We can perform DB joins with any column as long as the data type and the data value is matched.
  - AdrianB1 7y ago
    It depends on the version and how many modules you have installed. In my company it was ~ 50k tables for R/3, so the order of magnitude is right.
codegladiator 7y ago
3NF or more is good. Else its poor. Number of tables does not matter.
- silveroriole 7y ago
  There are good reasons to denormalise, but as a rule of thumb... yeah, this. I don’t think you can go that far wrong with schemas as long as you have an idea of your entities and their cardinalities. It’s much easier than designing, say, the associated Java classes, because there are clear rules about how to do it and it’s just obvious when you’ve done it wrong (your cardinality is all messed up).
OJFord 7y ago
> When I read about database schemas with thousands of tables I wonder what kind of problem could possibly require so many tables. They're probably mostly small (in terms of columns), many of them done just to have foreign key constraints, separate indexes, and arguably easier querying (joining) on just the data you need. But I think it's a particular style, rather than a 'problem' that 'requires' so many. (IANA database expert though, just my tuppence.)
hyperpallium 7y ago
diaclaimer: theoretical opinion. I think the primary problem of giving examples here is similar to teaching software engineering, which needs complex projects solving complex problems - too big for a semester project. A good schema depends on the problem it's solving. A secondary problem is similar to code that has sacrificed clarity for performance. The tweaks made for performance are not intrinsic to the logical problem, but are an additional constraint. For performance on common queries, schema can be flattened or "denomalized". The ability to do so was one of the original motivations for Codd's relational algebra.
adventured 7y ago
The Stackoverflow schema is decent. Especially for someone that is a relative amateur to study. It's nothing special or complex, however it's a good example of something that actually works well in practice (and at massive scale). The CRUD-CMS Q&A style lends itself nicely to a basic db schema that is easy to get your head around at a glance. https://meta.stackexchange.com/questions/2677/database-schema-documentation-for-the-public-data-dump-and-sede https://meta.stackexchange.com/questions/2677/database-schem... https://i.stack.imgur.com/AyIkW.png https://i.stack.imgur.com/AyIkW.png 19 million questions, 29m answers, 12m users, 10m visits per day, and most of the db action is in less than two dozen tables.
- felixyz 7y ago
  This is the only answer yet that seems to actually answer the question :) Great example!
- jaxn 7y ago
  Interesting to see the denormalization of user Display names on the most important tables, but not everywhere.
  - AdrianB1 7y ago
    Seeing some training sessions on performance tuning that use this database as the example, an educated guess is that it's done on purpose. I saw a few cases where usual rules have to be bent to get functionality, sometimes reality beats the book.
    - jaxn 7y ago
      Oh, I'm sure it is on purpose. I have had the same problem before and seeing this example makes me a little more comfortable to denormalize like that. I'm curious what they do if someone changes their name (though that should be really rare on SO)
- noisy_boy 7y ago
  Any idea which tool was used to generate the ER diagram?
mkettn 7y ago
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 try to keep things as tidy as possible.
- Faaak 7y ago
  I'd rather say: use an ORM ! It will design the DB schema better and faster than you. Still comprehensive enough
  - chrismeller 7y ago
    It also usually forces your design towards the entities themselves rather than the specific way they’re stored, which positions you better for switching to a completely different storage system in the future if, for instance, it’s becoming too slow or expensive to maintain everything in a traditional big name RDBMS.
    - felixyz 7y ago
      > forces your design towards the entities themselves I agree that it's very important to not let the physical schema leak into the rest of the system, and to have a strong conceptual model (aka entities and relations). This has been well understood for almost half a century: https://en.wikipedia.org/wiki/Three-schema_approach https://en.wikipedia.org/wiki/Three-schema_approach But I don't think ORMs are in any special position to help with this. They typically introduce so much other confusion that they tend to divert attention from designing both a good physical schema and a good conceptual model, and maintain a sensible mapping between the two. This can be done with RDBMS views for example, with a fraction of the overhead of an ORM. Most ORM-based code bases I've seen leak tons of db-level details. > switching to a completely different storage system in the future Designing for this eventuality is not healthy IMO. If you get there, it will be a so-called "good problem to have" and you will have to deal with whatever unique challenges you face at that level. We might as well be writing code with the possibility of "switching to a completely different programming language in the future" in mind. Yes, clean, modular code will help, but beyond that, not committing to the capabilities of the tools you have chosen will harm you system.
coltnz 7y ago
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"
- deepaksurti 7y ago
  >> "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+Volume+1%3A+A+Library+of+Universal+Data+Models+for+All+Enterprises%2C+Revised+Edition-p-9781118082324 https://www.wiley.com/en-us/The+Data+Model+Resource+Book%2C+...
  - davidw 7y ago
    As per my comment elsewhere, this open source project implements a lot of that book (I can second the recommendation): https://ofbiz.apache.org/ https://ofbiz.apache.org/
- felixyz 7y ago
  In a similar vein, I recommend David Hay's "Data Model Patternns: Conventions of Thought". It's more focused on the concept model side, but with awareness of how models are implemented in db schemas and the trade-offs involved.
ganonm 7y ago
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.
- GrumpyNl 7y ago
  Free download can be found here http://www.r-5.org/files/books/computers/languages/sql/style/Bill_Karwin-SQL_Antipatterns-EN.pdf http://www.r-5.org/files/books/computers/languages/sql/style...
  - ganonm 7y ago
    Please do not share links to pirated copies of resources such as this. I can't stop you from pirating it yourself, but it shouldn't be actively encouraged.
    - alharith 7y ago
      Just out of curiosity, why? Pirating traditional media, journals, and movies and entertainment is actively encouraged on this site, with popular sites and software for doing so frequently referenced, and it is never flagged. Seems like a double standard. (Full disclosure, I agree with your objection and believe piracy is wrong in any case, but seems like we should be consistent with the application of the rules)
      - dspillett 7y ago
        There is a difference between talking generally about piracy and its ramifications, even encouraging it as some would be doing, and directly linking to unlicensed copies of material. Even if an individual did believe that piracy is perfectly acceptable, direct linking still puts the site under risk of moving into the cross-hairs of law enforcement.
kohtatsu 7y ago
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/69bc0b5fe1ba943d82cd37fdbd23df6d#file-tag-yes-to-the-dress-syrup 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 https://github.com/nomsolence/syrup
- tasogare 7y ago
  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.
gerdusvz 7y ago
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]
- felixyz 7y ago
  100% The importance of having db schemas and other software entities reflect real-world things is not appreciated enough. It looks unimportant at first, but soon all intuition becomes useless and the system tends to not have "joints" in the right places, ie it doesn't have flexibility in the same places that reality does.
the-dude 7y ago
Northwind Traders
- dwd 7y ago
  That made me laugh. My 2 cents after doing this long enough to recognise it - Aim for 3NF but not religiously. Still, if you need a flat table try a view. - Any ternary relationship can be modeled as a pair of binary relations (you'll never regret keeping it simpler) - You don't need EAV (Magento is a good example of why you shouldn't) - On the other hand don't serialize data (looking at you WordPress) - XML and JSON data types though are perfectly fine when you need to store an object - Every table should have a primary key (preferably an integer) - If you really want a string for your primary key make it a candidate key (why, because someone will insist on changing it) - E/R diagrams are your friend - So are Venn diagrams for visualizing a complex select
Dwolb 7y ago
Dumb question, but does anyone have a recommendation for good software for generating the schema diagrams in the Drupal link but for Redshift?
- peelle 7y ago
  MySQL workbench will generate a diagram for mysql/mariadb databases.
arethuza 7y ago
"what kind of problem could possibly require so many tables" CRMs often have hundreds of tables and ERPs have thousands or tens of thousands or more.
semireg 7y ago
I’ve lived 20 years as an amateur SQL database designer and last year I designed my first production nosql schema on mongo. Nosql is a different world... collections, embedded documents. I’m not sure it’s 100% “correct” but it’s working great for me so far. My project is a license server for my electron app. The tech stack is a joy: mongo/mongoose, express, graphql, JWT, all with a React web front end. Payments by Stripe. The actual licenses are the signed JWT vended by the graphql API (not to be confused with the totally separate JWT for auth). The main goal is to sell software so I license by machine fingerprint (node module on electron). It’s been running for over 6 months without issue. I’m just beginning to start architecting an update where I allow a subscription model similar to Jetbrains Perpetual Fallback license, but slightly different in favor of the user. I’ve taken a lot of notes from comments at https://news.ycombinator.com/item?id=17007036 https://news.ycombinator.com/item?id=17007036 Here’s what I’m thinking so far: A) Non-Expiring License at $97.99. Unlimited bug fixes for that one version. or B) Expiring License at $9.79/month, and you always have access to the latest version. After 12 consecutive payments you are granted a Non-Expiring License for the most recent version at the time of each payment. Now, to model this...
femto113 7y ago
Take a look at Magento (open source e-commerce). I'll leave it to you to decide whether it's reducible to notions like "good" or "bad" but it's definitely fascinating in a WTF sort of way. https://i.stack.imgur.com/wnwrJ.jpg https://i.stack.imgur.com/wnwrJ.jpg
- madno 7y ago
  LOL, appreciate the link :D
singingwolfboy 7y ago
PostGraphile [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 one email address 2. Using PostgreSQL’s “row-level security” system to restrict database results based on the logged in user 3. Dividing tables across multiple schemas for additional security 4. SQL comments for documentation 5. SQL functions for common functionality, such as logging in or verifying an email It’s a fascinating project, and well worth a look! [1](https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/) [2](https://github.com/graphile/starter https://github.com/graphile/starter) [3](https://github.com/graphile/starter/blob/master/%40app/db/migrations/committed/000001.sql https://github.com/graphile/starter/blob/master/%40app/db/mi...)
- pimmen 7y ago
  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 been a safe assumption for a century that people share newspapers with each other, market research was done street by street to inform ad buyers of which markets we reached. Many people have more than one home. Some people take out another subscription for a relative. This mapped very awkwardly to digital subscribers who we had individual data on. We were able to join databases in a way that sort of works through more or less (mostly less) comfortable assumptions. The queries are not pretty.
  - jacques_chester 7y ago
    There's a whole subfield of information science dedicated to basically this exact problem: entity resolution. Hilariously, it has dozens of names, because it just comes up in so many places for so many people. It appears that "record linkage" is the term that has won the top spot at Wikipedia: https://en.wikipedia.org/wiki/Record_linkage https://en.wikipedia.org/wiki/Record_linkage
mshanu 7y ago
Too many tables in one schema tells me that there is no clear separation of responsibilities. I would first look at clear domain boundaries and separate them. Json type capability is one thing I have found useful when it comes to need for a non relational data to be stored simpler without creating associative tables.
fsajkdnjk 7y ago
i have been working with eventsourcing for the past few years and a design i have implemented in the repositories(db) lately is to have one table for events and one table for snapshots(ie. the objects in the current state in serialized form). then, depending on the needs of the application(ie. what queries will be run) I will create tables that will serve as pure indices by which I can then lookup the aggregates(object) I need. This gives me incredible flexibility and I do not need to bother with complex schema at all. I use event reactors within transaction context(imagine pre-save trigger per object) to fill these tables with data(or remove data). and from now on i think this is the way to go for me for anything. having your sql schema matching your objects/entities is very restricting and not flexible for future development. with this approach i have the full data available(snapshots) so i don't need to hydrate each aggregate from the event stream and i also have the ability to filter the aggregates as i need and also have highly optimized schema for any query i desire. when something changed in the future, i can simply play through the entire event stream and fill in new indices or whatever is needed. machines are fast these days so storing the entire object as snapshot in serialized form is nothing and it beats having to load tens of fields/columns and parse it into objects manually.
- madno 7y ago
  Nice pattern indeed. This articles explores when it is not the case to apply it (for example when eventual consistency would be a big problem): https://medium.com/@hugo.oliveira.rocha/what-they-dont-tell-you-about-event-sourcing-6afc23c69e9a https://medium.com/@hugo.oliveira.rocha/what-they-dont-tell-... I guess at some level of load (for scalability reasons) one might need to decouple writing into the append only event log from updating the tables for fast reads, so the one transaction approach won't work, hence the eventual consistency between written data and read data. Schema maintenance is also a non-trivial task as described in the article: keeping backward compatibility for various event versions, upcasting, lazy upcasting. Hitting a good granularity level for events is important, too big or too small, both are a problem. For the operational team without intimate app knowledge it is also harder to do ad-hoc work.
- janci 7y ago
  How do you solve event schema changes? I mean event has some data attached to it. The schema of this data may need to change in time. How to replay older events that do not match current schema? Do you keep all versions of event reactors to be able to replay old events?
iovrthoughtthis 7y ago
Where are the repositories of this info? Just for record keeping, analysis, learning etc.
gigatexal 7y ago
Read 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.
- inopinatus 7y ago
  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.
  - pbowyer 7y ago
    > 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 :)
  - edejong 7y ago
    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.
  - snarfy 7y ago
    You made a great example, and I think it's the most important thing to remember when designing schema. I've seen arguments for/against 1NF, 3NF, various db features, etc, but really, the schema should model the real world relationships. If your InvoiceItem has no description, and only has a FK to Product to get the description you're going to have a bad time. Once you build a system around a wrong relationship like that, it's very hard to go back and fix the inevitable issue that updating a product changes old sales invoice records.
    - KptMarchewa 7y ago
lifepillar 7y ago
Take a look at http://www.softwaregems.com/Documents/Student%20Resolutions/ http://www.softwaregems.com/Documents/Student%20Resolutions/. I will refrain to comment on the quality of Drupal’s schema, but that diagram just shows a bunch of tables and how those tables are connected by foreign keys. What do those connections mean? What other constraints are there in the data? Are they represented in the diagram? A good database design conveys a lot more semantics. There is currently one ISO standard (ISO 31320-2, key-based style modeling) for database design. Adop ting that standard does not automagically guarantee good designs, but, if used correctly, it helps a lot (it doesn’t help if you don’t have a good grasp of the Relational model, so I would recommend that you would get familiar with that first and foremost). Most database schemas you’ll find around are rippled with useless ID attributes that are essentially record identifiers (they are not even “surrogates” as many people call them: for the definition of a “surrogate” read Codd’s 1979 paper) and, as a consequence, they are navigational in nature (they resemble more CODASYL structures than Relational models): to connect records in table A with records in table D, you must join with (navigate through) B and C, while in a well-designed Relational schema you might have joined A and D directly. Do you want a rule of thumb to recognized poorly designed databases? Check for ID attributes in every table (there are many other ways in which database design can suck, though). How do you recognize good database diagrams? They can be easily translated into natural language (predicates and facts), and two (technically competent) different people would translate them in the same way, i.e., the model is unambiguous. Can you say so for Drupal’s schema?
- croisillon 7y ago
  your link ist just a directory?
- Pamar 7y ago
  I did a quick google search and ISO 31320-02 seems to be available only by paying ~170 CHF. Is there any text which explains how it is used? Even if I wanted to pay the aforementioned price I am afraid that I would get just a standard reference text with little or nothing in terms of actual teaching how is should be used and what the benefits are.
petercooper 7y ago
Vertabelo has a blog which is almost entirely devoted to modelling schemas for various contrived scenarios: https://www.vertabelo.com/blog/ https://www.vertabelo.com/blog/ .. I take umbrage with some of the decisions made but broadly they are interesting and well thought out.
madeofpalk 7y ago
> good database schema designs? Ones that ship.
- ComodoHacker 7y ago
  Ones that last, I'd say.
- collyw 7y ago
  I have worked on some shot database designs that managed to ship.
jacques_chester 7y ago
> 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.
- madno 7y ago
  Sounds like SAP :)
- mdellavo 7y ago
  or the systems arent multi-tenant so they end up cloning the schema for each customer
JacKTrocinskI 7y ago
1000 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.
- nujabe 7y ago
  Not a database person but 1000 tables does seem like an extraordinary amount imo. At that scale I wonder if such a use case warrants a document based database...
  - JacKTrocinskI 7y ago
    I have been working in the banking industry since 2015 as an Oracle database developer and contractor and having a few hundred or several thousand tables in a single database schema is typical. In a single ETL or ELT process you can have several different tables such as staging tables, configuration tables, tables for slowly changing dimensions, enrichment tables, configuration tables, temporary tables, tables for exchange partition, etc.
  - Mikhail_Edoshin 7y ago
    On a somewhat related note: the OpenType (OT) font format specifies 55 different "tables". These are not really what would be a table in a database but more like sections that may have multiple tables. E.g. the "math" OT table has constants, glyph info, italic corrections, top accent attachment, kern info, variants, glyph construction... -- I can't say for sure but I think a hundred tables to properly describe a font is probably a good estimate.
blowski 7y ago
There are three volumes of "The Data Model Resource Book" by Len Silverston, and despite being about 20 years old they remain industry best practices. All sorts of data model design patterns - insurance, ecommerce, data warehouses, party, finance, etc. David Hay's "Data Model Patterns" is also excellent, although less concrete.
tommaho 7y ago
The 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.
thrownaway954 7y ago
it really all depends on the requirements. i use to think wordpress had the worst schema in the world. after actually using it and writing plugins for it, i've come to the conclusion that it is genius. their schema design really makes it very easy for others to "extend" the structure without having to actually alter the schema. to elaborate on what i mean by extend, wordpress's schema is basically a post table that has the very minimal required columns for creating a post (like the title, date, status and a couple of other), the post_meta table references that post table and basically is used like a huge key/value store. really all it is post_id column (which reference back to the posts table), a key column and a value column. You can add whatever you want to a post by adding them to the post_meta. this design is copied for all other areas as well, such as users, comments and what have you. https://codex.wordpress.org/Database_Description https://codex.wordpress.org/Database_Description now obviously this kind of design isn't going to work for something like a financial institution where you most likely want a ton of referential integrity built into the schema to prevent accidental data lost and to validate data input, but for a CMS it works very well. again... it all depends on what the system requirements are.
- astockwell 7y ago
  After searching for years for what that design was called, I finally read somewhere that it’s the “property bag pattern”. Seemed fitting.
  - greggyb 7y ago
    I've referred to it, and been understand as entity-attribute-value, or EAV.
- pcnix 7y ago
  This is a database that satisfies at least the first three normal forms, and that might be why it felt comfortable. I admit that I have seen some dirty usage of this from a few WordPress plugin authors on a consultation, and it has since then not thought as greatly of it as a system that enforces good practices.
tundrax 7y ago
DatabaseAnswers[1] has been a valuable resource of learning for me. [1] http://www.databaseanswers.org/data_models/index.htm http://www.databaseanswers.org/data_models/index.htm
- harrybenz304 7y ago
  Datbase schema should be well organized and well managed
larrywright 7y ago
When you think about large enterprise software systems with screen after screen full of data entry forms, with drop down lists everywhere, every single one of those drop down selectors is likely a database table. Every schema like that will have a mix of large tables (customers, products, etc) and lots of small tables. I don’t think there’s a number above which you should not go, it’s really about how the data is organized and how easy it is to get data in and out of. I agree with another comment here about looking at anti-patterns first. There are some common ones that would be red flags. As an example of what not to do: I once worked with a DBA who insisted that tables shouldn’t have more than 15 columns. So any table with more than 15 columns would have to be split into multiple tables, even if those tables would always have to be fetched together.
xuebin100 7y ago
1
DanielBMarkham 7y ago
A schema is a way of designing data structures such that they are efficiently organized and easy to use. But that leaves the obvious question: use for what? Structures, whether it's databases or object graphs, exist for only two reasons: to do stuff and to fit into a pattern of rules you've decided to use beforehand. Without either of those, there is no way to judge a schema. We could talk about generally organizing data. That's set theory and normal forms. But that wasn't your question. I would design the schema for a three-microservice widely-distributed high-performance application far differently than I would a local checkbook app for my brother. You gotta have more information. Apologies. I am unable to help.
- pc86 7y ago
  > I would design the schema for a three-microservice widely-distributed high-performance application far differently than I would a local checkbook app for my brother. I think it might be helpful to expand on what some of those differences are. I think most of us here would be able to come up with at least a satisfactory schema for a local checkbook app. What are the changes you need to make to that type of schema to make it appropriate for a multi-service app, specifically? What about distributed? HP? Are any of these changes in conflict with each other? Etc
- HeroOfAges 7y ago
  I agree, and I've come to the conclusion that you should avoid designing a database schema until you have some clear understanding about how the application you're persisting data for will be used. The data structures and schemas will practically jump out at you. It seems, at this point in my career, a very obvious thing, and yet I cannot communicate this point of view in a way to my colleagues that will affect change.
  - nabeards 7y ago
    For my last few projects, I’ve started by building with a document-based database knowing a rewrite will come within 6-12 months. On rewrite, I can design the new relational db with 5NF very quickly.
  - 7y ago
ArtWomb 7y ago
I always think there is some magical perfect schema that I am missing. And if I just designed things perfectly in the beginning, I wouldn't need all this complex query logic. And reverse lookup tables, and fan outs, and other sundry hacks But action always trumps thought. And its better to just slurp up as much data as possible The simplest design for keys in a dict type data store such as Redis hashes, is to just auto_increment user ids. Resolve id=user. And then all data is just flat {var:id=value}. Key type is just a string delimited by ":". Gets you in the game fast. Mine structure and relationships later ;)
mamcx 7y ago
Ask this is like ask: Exist an example of a good collection of variables/classes in Java? For a rdbms, the schema IS the "classes, variables and types" on other languages. Only have the massive advantage of being much more visible and easier to understand.
auvi 7y ago
This is a bit off topic maybe, but I have heard SAP works with tens of thousands of tables. Is that correct?
- joshdick 7y ago
  A recent HN article answers this question, actually! "A basic installation of SAP has 20,000 database tables, 3,000 of which are configuration tables." https://news.ycombinator.com/item?id=22244750 https://news.ycombinator.com/item?id=22244750
  - auvi 7y ago
    Is the schema (even partially) available publicly?
pezo1919 7y ago
My naive approach these days is avoiding relational schemas, because I'm tired of migrations. Document based NoSQL is hard to maintain too (dupes). The only solution to me are Graph databases like Neo4j or GunDB (inmemory).
khanguy 7y ago
I've always liked how isfdb, the Internet Speculative Fiction Database, laid theirs out. http://www.isfdb.org/wiki/index.php/Database_Schema http://www.isfdb.org/wiki/index.php/Database_Schema
dougk16 7y ago
Not to be too self-promoty, but I spent a ton of time thinking about the schema for my site, and have gotten some compliments on the detail and documentation: https://aytwit.com/sql https://aytwit.com/sql I hope that helps spark some ideas for you.
joshdick 7y ago
WikiMedia's database schema is used by Wikipedia, one of the most popular websites there is, so it's arguably a very successful database schema design: https://www.mediawiki.org/w/index.php?title=Manual:Database_layout/diagram&action=render https://www.mediawiki.org/w/index.php?title=Manual:Database_...
iblaine 7y ago
> What are good schema examples? Anything that doesn't break the first normal form("1NF")[1]. > And what are some poor examples? Anything that breaks 1NF. You break 1NF when... * a column cannot be described by the key, ex. user.id describes user.name but not user.items_purchased. * values in a column are repeated, ex. a user table that stores multiple addresses for the same user should be split into an addresses table. Treat tables as classes and you'll be fine. Just like a method may not belong in a class, a column may not belong in a table. [1](https://en.wikipedia.org/wiki/First_normal_form https://en.wikipedia.org/wiki/First_normal_form)
- iblaine 7y ago
  s/1NF/3NF/g ...point here is that data good design comes from objects in 3NF...1NF is a good start, 3NF would be better...
davidw 7y ago
I've long recommended this book: "The Data Model Resource Book": https://amzn.to/2tXNiuF https://amzn.to/2tXNiuF You can look at an implementation of a number of the ideas here: https://ofbiz.apache.org/ https://ofbiz.apache.org/ It's kind of complicated - probably overly so for some things - but there are a lot of ideas to think about and maybe utilize at least in part.
deleted 7y ago
[deleted]
frye0031 7y ago
Take a look at StackOverflow’s schema. Freely available to study, used in a production site, and a great resource.
deleted 7y ago
[deleted]
jaequery 7y ago
I always wondered why people don't share SQL schemas like we do with codes. Anyone know if there's such a site like it or even a marketplace for it?
- konfusinomicon 7y ago
  This site is pretty solid from what I've dug into http://www.databaseanswers.org/data_models/index.htm http://www.databaseanswers.org/data_models/index.htm
derefr 7y ago
> I wonder what kind of problem could possibly require so many tables. There are three main factors behind this, none of which are bad schema design: 1. Table partitioning/sharding. RDBMS storage engines don't tend to scale for common hybrid access patterns (e.g. contended inserts+reads on tables with many indices and uniqueness constraints) past a billion rows or so. You can scale them yourself, though, by splitting a table's dataset into multiple tables (all with the same schema) and getting the RDBMS to direct reads/writes appropriately. 2. ETL ingest tables for separate datasources. If you have e.g. 100 different scrapers for various APIs used in your data warehouse, all writing into your DB, then even if they all are writing data with the same schema, it's operationally very challenging to have them write to the same physical table. Much simpler to have them write into their own staging tables, which can have their own exclusive-access locks, get their own deduping, get TRUNCATEd separately between batch loads, etc. They can copy into a shared table, but this step is often unnecessary and skipped. 3. Matviews built for extremely expensive queries that exist for running specific reports against. These are effectively materializataions of subqueries shared by common customer queries. As such, there might be hundreds or thousands of these. In the end, none of these are really tables ("relations", in the relational-algebra sense); they're all operational implementation details. Partitions are literally "parts" of larger tables; ETL ingest staging tables are distinct "inboxes" to a shared table; and reporting matviews are "caches" of queries. All of these things could be separate first-class object types, hidden inside the logic of the DBMS, not presented as "tables" per se, despite taking up storage space like tables. But—in most-any RDBMS I know of—they're not. So you see thousands of tables.
- mceachen 7y ago
  I think in the OP's case, they are staring at a dizzying number of tables for Drupal and wondering if the schema is "good." It's easy to create a clean schema for a simple problem. Drupal (and many other mature CMSes) are the result of a decade or more accumulation of features. Much like perl, "it's worse ... because people wanted it worse"[1]. 1: https://groups.google.com/forum/m/#!msg/comp.lang.perl.misc/5vPTrjRdnjY/88xwcrF5aA8J https://groups.google.com/forum/m/#!msg/comp.lang.perl.misc/...
  - 7y ago
sakopov 7y ago
Database Answers [1] has been around for years. It doesn't exactly have complicated schemas but there are enough examples to showcase various design patterns. [1] http://www.databaseanswers.org/data_models/ http://www.databaseanswers.org/data_models/
gfody 7y ago
A database schema is the result of practicing the art of information modeling - a timeless art that will unlock the deepest mysteries of the universe if you master it. When database threads hit HN it's basically disgusting how poorly understood this is and the bad advice that flows here. Everyone here is wrong wrong wrong wrong wrong wrong wrong wrong wrong wrong wrong............. apologies I'm not myself today, posting anyways, good luck!
bunchOfCucks 7y ago
Same ones as before.
38911BBF 7y ago
1-2-3NF is super important and superficially simple. But just to give you an example with 1NF where the devil is hidden in the deails, let's have a closer look at ye good old "Address" entity. If we are to store a postal address for package delivery the columns "Address Line 1-2-3" should suffice and enable quick entry in the UI and simple printing on letters and package labels. What's not to love? But what if you are working in a municipality and need to be able select all commercial buildings with more than five stories, or businesses situated in the basement of the building for the yearly fire inspections? Then string-searching all those randomly entered address lines will quickly become a complete nightmare - where as if the floor number was normalized and stored in its own column the query for the fire inspector's report would be a piece of cake. This is a good example of why it's so hard to do cookie-cutter-implementaion-ready-schema-design-templates. It's also a good example of why datamodeling is important no matter underlying tech-stack this data model is going to be implemented on. Also, I prefer modeling the app, business or process in Chen's ERD first as I think it is much better at capturing modeling details than UML and other ERD-variants. Also, just as each object class in OOP should do only one thing, each entity should be saved just one table. Eg. that "Employee" table in the first chapter of every beginner database book with a "Manager" relation as a foreign key to itself is an absolute catastrophe and very . The moment your CEO decides you are now in a matrix-organisation, everything breaks down datamodel-wise. The Employees go into one table, the Organisational Structure type into another - they are related by foreign keys and it's not that different from good OO modeling as people say. The tables containing organisational structure should probaly also have columns with a from- and to-date and a relationship to a Department table so different departments can be organised differently throughout their lifetime. Also, entities which have some sort of lifecycle should also be split into different tables. So there should be a table for "Prospective Employees", "Current Employees", "Resigned Employees", "Former Employees", etc. An employee's day of resignation can now be not null and go into the right table. You can always UNION these three or four tables together into one big view or materialized table, and at the same time you will avoid a massive amount of WHERE statements that each need their own indexes, picking out just the right employees from that big generic Employee table in every effing query. Also, columns with endless NULL values are a "code smell" in a relational database. Whatever value is hiding in the few rows with values probably belong to another entity and should have been stored in another table with the name of that "thing". Eg. the employee's day of resignation again. Also, 99% of all business datamodels can be implemented in a relational database using just tables, views and queries created in standard SQL. You will rarely if ever need user defined functions, generators, connectors, stored procedures, foreign code blobs and other exotic and vendor specific extensions. Also, I recommend reading everything by Joe Celko.

felixyz 7y ago

The Sakila schema: https://dev.mysql.com/doc/sakila/en/ https://dev.mysql.com/doc/sakila/en/ Which I learned about from the jOOQ folks: https://www.jooq.org/sakila https://www.jooq.org/sakila

davismwfl 7y ago

Kinda tough to give a good answer without more context, IMO. What I mean is that a good e-commerce schema that serves a single small store and runs off a single database server would look quite different then a multi-tenant or distributed data store for a e-commerce site at scale. The one you linked is a pretty typical relational model and isn't bad, but it has trade offs that I'd personally not make, however, that doesn't make it bad. In the end context, scale and usage all determine a good schema design. Sometimes what would be a good relational design on paper would be tragically horrid in practice once you get beyond a small dataset.

billfruit 7y ago
Yes, but where can we all see the schemas that has worked well and the ones which hasn't, I mean all that information isn't accessible to someone who wants to know the state of the art. In someways software industry has been really bad in documenting and sharing knowledge, it is really hard to get hold of documents of large software projects, and informed commentary on them from the industry for educational purposes, I wish someone would do something about it.
- davismwfl 7y ago
  Very fair and very true, and I am 100% in agreement with your points, I do not know of a single resource we can go to as an educational toolset that is based on real world products/experience. The problem is the industry as a whole is locked into these NDA type agreements (along with proprietary information) which prevent a lot of the complete schema sharing etc which would help people learn the best. That said, some of the best things to look at are open source projects and research their pain points and highlights. That will many times point you to the tradeoffs they made in modeling and in code. For e-commerce things like magento and other open source solutions can be really informative, even woocommerce and how it shoehorns itself into wordpress can be informative as to the tradeoffs on highly normalized vs lightly normalized vs key/values etc.

Namoshek 7y ago

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/ https://retool.com/blog/erp-for-engineers/)

arethuza 7y ago
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...
- gberger 7y ago
  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?
  - dizaime 7y ago
    Sure it is. We can perform DB joins with any column as long as the data type and the data value is matched.
- AdrianB1 7y ago
  It depends on the version and how many modules you have installed. In my company it was ~ 50k tables for R/3, so the order of magnitude is right.

codegladiator 7y ago

3NF or more is good. Else its poor. Number of tables does not matter.

silveroriole 7y ago
There are good reasons to denormalise, but as a rule of thumb... yeah, this. I don’t think you can go that far wrong with schemas as long as you have an idea of your entities and their cardinalities. It’s much easier than designing, say, the associated Java classes, because there are clear rules about how to do it and it’s just obvious when you’ve done it wrong (your cardinality is all messed up).

OJFord 7y ago

> When I read about database schemas with thousands of tables I wonder what kind of problem could possibly require so many tables. They're probably mostly small (in terms of columns), many of them done just to have foreign key constraints, separate indexes, and arguably easier querying (joining) on just the data you need. But I think it's a particular style, rather than a 'problem' that 'requires' so many. (IANA database expert though, just my tuppence.)

hyperpallium 7y ago

diaclaimer: theoretical opinion. I think the primary problem of giving examples here is similar to teaching software engineering, which needs complex projects solving complex problems - too big for a semester project. A good schema depends on the problem it's solving. A secondary problem is similar to code that has sacrificed clarity for performance. The tweaks made for performance are not intrinsic to the logical problem, but are an additional constraint. For performance on common queries, schema can be flattened or "denomalized". The ability to do so was one of the original motivations for Codd's relational algebra.

adventured 7y ago

The Stackoverflow schema is decent. Especially for someone that is a relative amateur to study. It's nothing special or complex, however it's a good example of something that actually works well in practice (and at massive scale). The CRUD-CMS Q&A style lends itself nicely to a basic db schema that is easy to get your head around at a glance. https://meta.stackexchange.com/questions/2677/database-schema-documentation-for-the-public-data-dump-and-sede https://meta.stackexchange.com/questions/2677/database-schem... https://i.stack.imgur.com/AyIkW.png https://i.stack.imgur.com/AyIkW.png 19 million questions, 29m answers, 12m users, 10m visits per day, and most of the db action is in less than two dozen tables.

felixyz 7y ago
This is the only answer yet that seems to actually answer the question :) Great example!
jaxn 7y ago
Interesting to see the denormalization of user Display names on the most important tables, but not everywhere.
- AdrianB1 7y ago
  Seeing some training sessions on performance tuning that use this database as the example, an educated guess is that it's done on purpose. I saw a few cases where usual rules have to be bent to get functionality, sometimes reality beats the book.
  - jaxn 7y ago
    Oh, I'm sure it is on purpose. I have had the same problem before and seeing this example makes me a little more comfortable to denormalize like that. I'm curious what they do if someone changes their name (though that should be really rare on SO)
noisy_boy 7y ago
Any idea which tool was used to generate the ER diagram?

mkettn 7y ago

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 try to keep things as tidy as possible.

Faaak 7y ago
I'd rather say: use an ORM ! It will design the DB schema better and faster than you. Still comprehensive enough
- chrismeller 7y ago
  It also usually forces your design towards the entities themselves rather than the specific way they’re stored, which positions you better for switching to a completely different storage system in the future if, for instance, it’s becoming too slow or expensive to maintain everything in a traditional big name RDBMS.
  - felixyz 7y ago
    > forces your design towards the entities themselves I agree that it's very important to not let the physical schema leak into the rest of the system, and to have a strong conceptual model (aka entities and relations). This has been well understood for almost half a century: https://en.wikipedia.org/wiki/Three-schema_approach https://en.wikipedia.org/wiki/Three-schema_approach But I don't think ORMs are in any special position to help with this. They typically introduce so much other confusion that they tend to divert attention from designing both a good physical schema and a good conceptual model, and maintain a sensible mapping between the two. This can be done with RDBMS views for example, with a fraction of the overhead of an ORM. Most ORM-based code bases I've seen leak tons of db-level details. > switching to a completely different storage system in the future Designing for this eventuality is not healthy IMO. If you get there, it will be a so-called "good problem to have" and you will have to deal with whatever unique challenges you face at that level. We might as well be writing code with the possibility of "switching to a completely different programming language in the future" in mind. Yes, clean, modular code will help, but beyond that, not committing to the capabilities of the tools you have chosen will harm you system.

coltnz 7y ago

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"

deepaksurti 7y ago
>> "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+Volume+1%3A+A+Library+of+Universal+Data+Models+for+All+Enterprises%2C+Revised+Edition-p-9781118082324 https://www.wiley.com/en-us/The+Data+Model+Resource+Book%2C+...
- davidw 7y ago
  As per my comment elsewhere, this open source project implements a lot of that book (I can second the recommendation): https://ofbiz.apache.org/ https://ofbiz.apache.org/
felixyz 7y ago
In a similar vein, I recommend David Hay's "Data Model Patternns: Conventions of Thought". It's more focused on the concept model side, but with awareness of how models are implemented in db schemas and the trade-offs involved.

ganonm 7y ago

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.

GrumpyNl 7y ago
Free download can be found here http://www.r-5.org/files/books/computers/languages/sql/style/Bill_Karwin-SQL_Antipatterns-EN.pdf http://www.r-5.org/files/books/computers/languages/sql/style...
- ganonm 7y ago
  Please do not share links to pirated copies of resources such as this. I can't stop you from pirating it yourself, but it shouldn't be actively encouraged.
  - alharith 7y ago
    Just out of curiosity, why? Pirating traditional media, journals, and movies and entertainment is actively encouraged on this site, with popular sites and software for doing so frequently referenced, and it is never flagged. Seems like a double standard. (Full disclosure, I agree with your objection and believe piracy is wrong in any case, but seems like we should be consistent with the application of the rules)
    - dspillett 7y ago
      There is a difference between talking generally about piracy and its ramifications, even encouraging it as some would be doing, and directly linking to unlicensed copies of material. Even if an individual did believe that piracy is perfectly acceptable, direct linking still puts the site under risk of moving into the cross-hairs of law enforcement.

kohtatsu 7y ago

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/69bc0b5fe1ba943d82cd37fdbd23df6d#file-tag-yes-to-the-dress-syrup 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 https://github.com/nomsolence/syrup

tasogare 7y ago
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.

gerdusvz 7y ago

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]

felixyz 7y ago
100% The importance of having db schemas and other software entities reflect real-world things is not appreciated enough. It looks unimportant at first, but soon all intuition becomes useless and the system tends to not have "joints" in the right places, ie it doesn't have flexibility in the same places that reality does.

the-dude 7y ago

Northwind Traders

dwd 7y ago
That made me laugh. My 2 cents after doing this long enough to recognise it - Aim for 3NF but not religiously. Still, if you need a flat table try a view. - Any ternary relationship can be modeled as a pair of binary relations (you'll never regret keeping it simpler) - You don't need EAV (Magento is a good example of why you shouldn't) - On the other hand don't serialize data (looking at you WordPress) - XML and JSON data types though are perfectly fine when you need to store an object - Every table should have a primary key (preferably an integer) - If you really want a string for your primary key make it a candidate key (why, because someone will insist on changing it) - E/R diagrams are your friend - So are Venn diagrams for visualizing a complex select

Dwolb 7y ago

Dumb question, but does anyone have a recommendation for good software for generating the schema diagrams in the Drupal link but for Redshift?

peelle 7y ago
MySQL workbench will generate a diagram for mysql/mariadb databases.

arethuza 7y ago

"what kind of problem could possibly require so many tables" CRMs often have hundreds of tables and ERPs have thousands or tens of thousands or more.

semireg 7y ago

I’ve lived 20 years as an amateur SQL database designer and last year I designed my first production nosql schema on mongo. Nosql is a different world... collections, embedded documents. I’m not sure it’s 100% “correct” but it’s working great for me so far. My project is a license server for my electron app. The tech stack is a joy: mongo/mongoose, express, graphql, JWT, all with a React web front end. Payments by Stripe. The actual licenses are the signed JWT vended by the graphql API (not to be confused with the totally separate JWT for auth). The main goal is to sell software so I license by machine fingerprint (node module on electron). It’s been running for over 6 months without issue. I’m just beginning to start architecting an update where I allow a subscription model similar to Jetbrains Perpetual Fallback license, but slightly different in favor of the user. I’ve taken a lot of notes from comments at https://news.ycombinator.com/item?id=17007036 https://news.ycombinator.com/item?id=17007036 Here’s what I’m thinking so far: A) Non-Expiring License at $97.99. Unlimited bug fixes for that one version. or B) Expiring License at $9.79/month, and you always have access to the latest version. After 12 consecutive payments you are granted a Non-Expiring License for the most recent version at the time of each payment. Now, to model this...

femto113 7y ago

Take a look at Magento (open source e-commerce). I'll leave it to you to decide whether it's reducible to notions like "good" or "bad" but it's definitely fascinating in a WTF sort of way. https://i.stack.imgur.com/wnwrJ.jpg https://i.stack.imgur.com/wnwrJ.jpg

madno 7y ago
LOL, appreciate the link :D

singingwolfboy 7y ago

PostGraphile [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 one email address 2. Using PostgreSQL’s “row-level security” system to restrict database results based on the logged in user 3. Dividing tables across multiple schemas for additional security 4. SQL comments for documentation 5. SQL functions for common functionality, such as logging in or verifying an email It’s a fascinating project, and well worth a look! [1](https://www.graphile.org/postgraphile/ https://www.graphile.org/postgraphile/) [2](https://github.com/graphile/starter https://github.com/graphile/starter) [3](https://github.com/graphile/starter/blob/master/%40app/db/migrations/committed/000001.sql https://github.com/graphile/starter/blob/master/%40app/db/mi...)

pimmen 7y ago
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 been a safe assumption for a century that people share newspapers with each other, market research was done street by street to inform ad buyers of which markets we reached. Many people have more than one home. Some people take out another subscription for a relative. This mapped very awkwardly to digital subscribers who we had individual data on. We were able to join databases in a way that sort of works through more or less (mostly less) comfortable assumptions. The queries are not pretty.
- jacques_chester 7y ago
  There's a whole subfield of information science dedicated to basically this exact problem: entity resolution. Hilariously, it has dozens of names, because it just comes up in so many places for so many people. It appears that "record linkage" is the term that has won the top spot at Wikipedia: https://en.wikipedia.org/wiki/Record_linkage https://en.wikipedia.org/wiki/Record_linkage

mshanu 7y ago

Too many tables in one schema tells me that there is no clear separation of responsibilities. I would first look at clear domain boundaries and separate them. Json type capability is one thing I have found useful when it comes to need for a non relational data to be stored simpler without creating associative tables.

fsajkdnjk 7y ago

i have been working with eventsourcing for the past few years and a design i have implemented in the repositories(db) lately is to have one table for events and one table for snapshots(ie. the objects in the current state in serialized form). then, depending on the needs of the application(ie. what queries will be run) I will create tables that will serve as pure indices by which I can then lookup the aggregates(object) I need. This gives me incredible flexibility and I do not need to bother with complex schema at all. I use event reactors within transaction context(imagine pre-save trigger per object) to fill these tables with data(or remove data). and from now on i think this is the way to go for me for anything. having your sql schema matching your objects/entities is very restricting and not flexible for future development. with this approach i have the full data available(snapshots) so i don't need to hydrate each aggregate from the event stream and i also have the ability to filter the aggregates as i need and also have highly optimized schema for any query i desire. when something changed in the future, i can simply play through the entire event stream and fill in new indices or whatever is needed. machines are fast these days so storing the entire object as snapshot in serialized form is nothing and it beats having to load tens of fields/columns and parse it into objects manually.

madno 7y ago
Nice pattern indeed. This articles explores when it is not the case to apply it (for example when eventual consistency would be a big problem): https://medium.com/@hugo.oliveira.rocha/what-they-dont-tell-you-about-event-sourcing-6afc23c69e9a https://medium.com/@hugo.oliveira.rocha/what-they-dont-tell-... I guess at some level of load (for scalability reasons) one might need to decouple writing into the append only event log from updating the tables for fast reads, so the one transaction approach won't work, hence the eventual consistency between written data and read data. Schema maintenance is also a non-trivial task as described in the article: keeping backward compatibility for various event versions, upcasting, lazy upcasting. Hitting a good granularity level for events is important, too big or too small, both are a problem. For the operational team without intimate app knowledge it is also harder to do ad-hoc work.
janci 7y ago
How do you solve event schema changes? I mean event has some data attached to it. The schema of this data may need to change in time. How to replay older events that do not match current schema? Do you keep all versions of event reactors to be able to replay old events?

iovrthoughtthis 7y ago

Where are the repositories of this info? Just for record keeping, analysis, learning etc.

gigatexal 7y ago

Read 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.

inopinatus 7y ago
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.
- pbowyer 7y ago
  > 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 :)
- edejong 7y ago
  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.
- snarfy 7y ago
  You made a great example, and I think it's the most important thing to remember when designing schema. I've seen arguments for/against 1NF, 3NF, various db features, etc, but really, the schema should model the real world relationships. If your InvoiceItem has no description, and only has a FK to Product to get the description you're going to have a bad time. Once you build a system around a wrong relationship like that, it's very hard to go back and fix the inevitable issue that updating a product changes old sales invoice records.
  - KptMarchewa 7y ago

lifepillar 7y ago

Take a look at http://www.softwaregems.com/Documents/Student%20Resolutions/ http://www.softwaregems.com/Documents/Student%20Resolutions/. I will refrain to comment on the quality of Drupal’s schema, but that diagram just shows a bunch of tables and how those tables are connected by foreign keys. What do those connections mean? What other constraints are there in the data? Are they represented in the diagram? A good database design conveys a lot more semantics. There is currently one ISO standard (ISO 31320-2, key-based style modeling) for database design. Adop ting that standard does not automagically guarantee good designs, but, if used correctly, it helps a lot (it doesn’t help if you don’t have a good grasp of the Relational model, so I would recommend that you would get familiar with that first and foremost). Most database schemas you’ll find around are rippled with useless ID attributes that are essentially record identifiers (they are not even “surrogates” as many people call them: for the definition of a “surrogate” read Codd’s 1979 paper) and, as a consequence, they are navigational in nature (they resemble more CODASYL structures than Relational models): to connect records in table A with records in table D, you must join with (navigate through) B and C, while in a well-designed Relational schema you might have joined A and D directly. Do you want a rule of thumb to recognized poorly designed databases? Check for ID attributes in every table (there are many other ways in which database design can suck, though). How do you recognize good database diagrams? They can be easily translated into natural language (predicates and facts), and two (technically competent) different people would translate them in the same way, i.e., the model is unambiguous. Can you say so for Drupal’s schema?

croisillon 7y ago
your link ist just a directory?
Pamar 7y ago
I did a quick google search and ISO 31320-02 seems to be available only by paying ~170 CHF. Is there any text which explains how it is used? Even if I wanted to pay the aforementioned price I am afraid that I would get just a standard reference text with little or nothing in terms of actual teaching how is should be used and what the benefits are.

petercooper 7y ago

Vertabelo has a blog which is almost entirely devoted to modelling schemas for various contrived scenarios: https://www.vertabelo.com/blog/ https://www.vertabelo.com/blog/ .. I take umbrage with some of the decisions made but broadly they are interesting and well thought out.

madeofpalk 7y ago

> good database schema designs? Ones that ship.

ComodoHacker 7y ago
Ones that last, I'd say.
collyw 7y ago
I have worked on some shot database designs that managed to ship.

jacques_chester 7y ago

> 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.

madno 7y ago
Sounds like SAP :)
mdellavo 7y ago
or the systems arent multi-tenant so they end up cloning the schema for each customer

JacKTrocinskI 7y ago

1000 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.

nujabe 7y ago
Not a database person but 1000 tables does seem like an extraordinary amount imo. At that scale I wonder if such a use case warrants a document based database...
- JacKTrocinskI 7y ago
  I have been working in the banking industry since 2015 as an Oracle database developer and contractor and having a few hundred or several thousand tables in a single database schema is typical. In a single ETL or ELT process you can have several different tables such as staging tables, configuration tables, tables for slowly changing dimensions, enrichment tables, configuration tables, temporary tables, tables for exchange partition, etc.
- Mikhail_Edoshin 7y ago
  On a somewhat related note: the OpenType (OT) font format specifies 55 different "tables". These are not really what would be a table in a database but more like sections that may have multiple tables. E.g. the "math" OT table has constants, glyph info, italic corrections, top accent attachment, kern info, variants, glyph construction... -- I can't say for sure but I think a hundred tables to properly describe a font is probably a good estimate.

blowski 7y ago

There are three volumes of "The Data Model Resource Book" by Len Silverston, and despite being about 20 years old they remain industry best practices. All sorts of data model design patterns - insurance, ecommerce, data warehouses, party, finance, etc. David Hay's "Data Model Patterns" is also excellent, although less concrete.

tommaho 7y ago

The 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.

thrownaway954 7y ago

it really all depends on the requirements. i use to think wordpress had the worst schema in the world. after actually using it and writing plugins for it, i've come to the conclusion that it is genius. their schema design really makes it very easy for others to "extend" the structure without having to actually alter the schema. to elaborate on what i mean by extend, wordpress's schema is basically a post table that has the very minimal required columns for creating a post (like the title, date, status and a couple of other), the post_meta table references that post table and basically is used like a huge key/value store. really all it is post_id column (which reference back to the posts table), a key column and a value column. You can add whatever you want to a post by adding them to the post_meta. this design is copied for all other areas as well, such as users, comments and what have you. https://codex.wordpress.org/Database_Description https://codex.wordpress.org/Database_Description now obviously this kind of design isn't going to work for something like a financial institution where you most likely want a ton of referential integrity built into the schema to prevent accidental data lost and to validate data input, but for a CMS it works very well. again... it all depends on what the system requirements are.

astockwell 7y ago
After searching for years for what that design was called, I finally read somewhere that it’s the “property bag pattern”. Seemed fitting.
- greggyb 7y ago
  I've referred to it, and been understand as entity-attribute-value, or EAV.
pcnix 7y ago
This is a database that satisfies at least the first three normal forms, and that might be why it felt comfortable. I admit that I have seen some dirty usage of this from a few WordPress plugin authors on a consultation, and it has since then not thought as greatly of it as a system that enforces good practices.

tundrax 7y ago

DatabaseAnswers[1] has been a valuable resource of learning for me. [1] http://www.databaseanswers.org/data_models/index.htm http://www.databaseanswers.org/data_models/index.htm

harrybenz304 7y ago
Datbase schema should be well organized and well managed

larrywright 7y ago

When you think about large enterprise software systems with screen after screen full of data entry forms, with drop down lists everywhere, every single one of those drop down selectors is likely a database table. Every schema like that will have a mix of large tables (customers, products, etc) and lots of small tables. I don’t think there’s a number above which you should not go, it’s really about how the data is organized and how easy it is to get data in and out of. I agree with another comment here about looking at anti-patterns first. There are some common ones that would be red flags. As an example of what not to do: I once worked with a DBA who insisted that tables shouldn’t have more than 15 columns. So any table with more than 15 columns would have to be split into multiple tables, even if those tables would always have to be fetched together.

xuebin100 7y ago

DanielBMarkham 7y ago

A schema is a way of designing data structures such that they are efficiently organized and easy to use. But that leaves the obvious question: use for what? Structures, whether it's databases or object graphs, exist for only two reasons: to do stuff and to fit into a pattern of rules you've decided to use beforehand. Without either of those, there is no way to judge a schema. We could talk about generally organizing data. That's set theory and normal forms. But that wasn't your question. I would design the schema for a three-microservice widely-distributed high-performance application far differently than I would a local checkbook app for my brother. You gotta have more information. Apologies. I am unable to help.

pc86 7y ago
> I would design the schema for a three-microservice widely-distributed high-performance application far differently than I would a local checkbook app for my brother. I think it might be helpful to expand on what some of those differences are. I think most of us here would be able to come up with at least a satisfactory schema for a local checkbook app. What are the changes you need to make to that type of schema to make it appropriate for a multi-service app, specifically? What about distributed? HP? Are any of these changes in conflict with each other? Etc
HeroOfAges 7y ago
I agree, and I've come to the conclusion that you should avoid designing a database schema until you have some clear understanding about how the application you're persisting data for will be used. The data structures and schemas will practically jump out at you. It seems, at this point in my career, a very obvious thing, and yet I cannot communicate this point of view in a way to my colleagues that will affect change.
- nabeards 7y ago
  For my last few projects, I’ve started by building with a document-based database knowing a rewrite will come within 6-12 months. On rewrite, I can design the new relational db with 5NF very quickly.
- 7y ago

ArtWomb 7y ago

I always think there is some magical perfect schema that I am missing. And if I just designed things perfectly in the beginning, I wouldn't need all this complex query logic. And reverse lookup tables, and fan outs, and other sundry hacks But action always trumps thought. And its better to just slurp up as much data as possible The simplest design for keys in a dict type data store such as Redis hashes, is to just auto_increment user ids. Resolve id=user. And then all data is just flat {var:id=value}. Key type is just a string delimited by ":". Gets you in the game fast. Mine structure and relationships later ;)

mamcx 7y ago

Ask this is like ask: Exist an example of a good collection of variables/classes in Java? For a rdbms, the schema IS the "classes, variables and types" on other languages. Only have the massive advantage of being much more visible and easier to understand.

auvi 7y ago

This is a bit off topic maybe, but I have heard SAP works with tens of thousands of tables. Is that correct?

joshdick 7y ago
A recent HN article answers this question, actually! "A basic installation of SAP has 20,000 database tables, 3,000 of which are configuration tables." https://news.ycombinator.com/item?id=22244750 https://news.ycombinator.com/item?id=22244750
- auvi 7y ago
  Is the schema (even partially) available publicly?

pezo1919 7y ago

My naive approach these days is avoiding relational schemas, because I'm tired of migrations. Document based NoSQL is hard to maintain too (dupes). The only solution to me are Graph databases like Neo4j or GunDB (inmemory).

khanguy 7y ago

I've always liked how isfdb, the Internet Speculative Fiction Database, laid theirs out. http://www.isfdb.org/wiki/index.php/Database_Schema http://www.isfdb.org/wiki/index.php/Database_Schema

dougk16 7y ago

Not to be too self-promoty, but I spent a ton of time thinking about the schema for my site, and have gotten some compliments on the detail and documentation: https://aytwit.com/sql https://aytwit.com/sql I hope that helps spark some ideas for you.

joshdick 7y ago

WikiMedia's database schema is used by Wikipedia, one of the most popular websites there is, so it's arguably a very successful database schema design: https://www.mediawiki.org/w/index.php?title=Manual:Database_layout/diagram&action=render https://www.mediawiki.org/w/index.php?title=Manual:Database_...

iblaine 7y ago

> What are good schema examples? Anything that doesn't break the first normal form("1NF")[1]. > And what are some poor examples? Anything that breaks 1NF. You break 1NF when... * a column cannot be described by the key, ex. user.id describes user.name but not user.items_purchased. * values in a column are repeated, ex. a user table that stores multiple addresses for the same user should be split into an addresses table. Treat tables as classes and you'll be fine. Just like a method may not belong in a class, a column may not belong in a table. [1](https://en.wikipedia.org/wiki/First_normal_form https://en.wikipedia.org/wiki/First_normal_form)

iblaine 7y ago
s/1NF/3NF/g ...point here is that data good design comes from objects in 3NF...1NF is a good start, 3NF would be better...

davidw 7y ago

I've long recommended this book: "The Data Model Resource Book": https://amzn.to/2tXNiuF https://amzn.to/2tXNiuF You can look at an implementation of a number of the ideas here: https://ofbiz.apache.org/ https://ofbiz.apache.org/ It's kind of complicated - probably overly so for some things - but there are a lot of ideas to think about and maybe utilize at least in part.

deleted 7y ago

[deleted]

frye0031 7y ago

Take a look at StackOverflow’s schema. Freely available to study, used in a production site, and a great resource.

deleted 7y ago

[deleted]

jaequery 7y ago

I always wondered why people don't share SQL schemas like we do with codes. Anyone know if there's such a site like it or even a marketplace for it?

konfusinomicon 7y ago
This site is pretty solid from what I've dug into http://www.databaseanswers.org/data_models/index.htm http://www.databaseanswers.org/data_models/index.htm

derefr 7y ago

> I wonder what kind of problem could possibly require so many tables. There are three main factors behind this, none of which are bad schema design: 1. Table partitioning/sharding. RDBMS storage engines don't tend to scale for common hybrid access patterns (e.g. contended inserts+reads on tables with many indices and uniqueness constraints) past a billion rows or so. You can scale them yourself, though, by splitting a table's dataset into multiple tables (all with the same schema) and getting the RDBMS to direct reads/writes appropriately. 2. ETL ingest tables for separate datasources. If you have e.g. 100 different scrapers for various APIs used in your data warehouse, all writing into your DB, then even if they all are writing data with the same schema, it's operationally very challenging to have them write to the same physical table. Much simpler to have them write into their own staging tables, which can have their own exclusive-access locks, get their own deduping, get TRUNCATEd separately between batch loads, etc. They can copy into a shared table, but this step is often unnecessary and skipped. 3. Matviews built for extremely expensive queries that exist for running specific reports against. These are effectively materializataions of subqueries shared by common customer queries. As such, there might be hundreds or thousands of these. In the end, none of these are really tables ("relations", in the relational-algebra sense); they're all operational implementation details. Partitions are literally "parts" of larger tables; ETL ingest staging tables are distinct "inboxes" to a shared table; and reporting matviews are "caches" of queries. All of these things could be separate first-class object types, hidden inside the logic of the DBMS, not presented as "tables" per se, despite taking up storage space like tables. But—in most-any RDBMS I know of—they're not. So you see thousands of tables.

mceachen 7y ago
I think in the OP's case, they are staring at a dizzying number of tables for Drupal and wondering if the schema is "good." It's easy to create a clean schema for a simple problem. Drupal (and many other mature CMSes) are the result of a decade or more accumulation of features. Much like perl, "it's worse ... because people wanted it worse"[1]. 1: https://groups.google.com/forum/m/#!msg/comp.lang.perl.misc/5vPTrjRdnjY/88xwcrF5aA8J https://groups.google.com/forum/m/#!msg/comp.lang.perl.misc/...
- 7y ago

sakopov 7y ago

Database Answers [1] has been around for years. It doesn't exactly have complicated schemas but there are enough examples to showcase various design patterns. [1] http://www.databaseanswers.org/data_models/ http://www.databaseanswers.org/data_models/

gfody 7y ago

A database schema is the result of practicing the art of information modeling - a timeless art that will unlock the deepest mysteries of the universe if you master it. When database threads hit HN it's basically disgusting how poorly understood this is and the bad advice that flows here. Everyone here is wrong wrong wrong wrong wrong wrong wrong wrong wrong wrong wrong............. apologies I'm not myself today, posting anyways, good luck!

bunchOfCucks 7y ago

Same ones as before.

38911BBF 7y ago

1-2-3NF is super important and superficially simple. But just to give you an example with 1NF where the devil is hidden in the deails, let's have a closer look at ye good old "Address" entity. If we are to store a postal address for package delivery the columns "Address Line 1-2-3" should suffice and enable quick entry in the UI and simple printing on letters and package labels. What's not to love? But what if you are working in a municipality and need to be able select all commercial buildings with more than five stories, or businesses situated in the basement of the building for the yearly fire inspections? Then string-searching all those randomly entered address lines will quickly become a complete nightmare - where as if the floor number was normalized and stored in its own column the query for the fire inspector's report would be a piece of cake. This is a good example of why it's so hard to do cookie-cutter-implementaion-ready-schema-design-templates. It's also a good example of why datamodeling is important no matter underlying tech-stack this data model is going to be implemented on. Also, I prefer modeling the app, business or process in Chen's ERD first as I think it is much better at capturing modeling details than UML and other ERD-variants. Also, just as each object class in OOP should do only one thing, each entity should be saved just one table. Eg. that "Employee" table in the first chapter of every beginner database book with a "Manager" relation as a foreign key to itself is an absolute catastrophe and very . The moment your CEO decides you are now in a matrix-organisation, everything breaks down datamodel-wise. The Employees go into one table, the Organisational Structure type into another - they are related by foreign keys and it's not that different from good OO modeling as people say. The tables containing organisational structure should probaly also have columns with a from- and to-date and a relationship to a Department table so different departments can be organised differently throughout their lifetime. Also, entities which have some sort of lifecycle should also be split into different tables. So there should be a table for "Prospective Employees", "Current Employees", "Resigned Employees", "Former Employees", etc. An employee's day of resignation can now be not null and go into the right table. You can always UNION these three or four tables together into one big view or materialized table, and at the same time you will avoid a massive amount of WHERE statements that each need their own indexes, picking out just the right employees from that big generic Employee table in every effing query. Also, columns with endless NULL values are a "code smell" in a relational database. Whatever value is hiding in the few rows with values probably belong to another entity and should have been stored in another table with the name of that "thing". Eg. the employee's day of resignation again. Also, 99% of all business datamodels can be implemented in a relational database using just tables, views and queries created in standard SQL. You will rarely if ever need user defined functions, generators, connectors, stored procedures, foreign code blobs and other exotic and vendor specific extensions. Also, I recommend reading everything by Joe Celko.