Voice of the DBA

Tag: database design

  • Singular or Plural

    There are all sorts of interesting debates that developers will have about programming. One of those I’ve written about in the past is Spaces v Tabs. There’s a lot of discussion about that one, and certainly no share of strong opinions. I’m sure there are plenty of other development paradigms and habits that will create debate, arguments, and perhaps strain some working relationships.

    While I’m not looking to upset anyone, I ran across a discussion recently and thought this would make an interesting debate. If you’ve got an opinion, please share how and why you might choose to follow your convention. Examples are helpful and may enhance the reasons why you go choose to build systems one way or the other.

    If you examine any sample databases out there, you will run across tables named like this: Customers, Orders, Cities, etc. You might also find Product, Person, Address as well. If you are paying attention, you might notice that my examples are both singular and plural in their form. There are no shortage of debates on the topic, but I’m wondering what many of you think.

    I’ve tended to build tables with plural nouns in the past, but I think that’s because the first few people that taught me did that. As I’ve read more and listened to others explain their design decisions, I’ve embraced singular names. After all, the entity being modeled is often a single instance of a type. A Person or a CreditCard, not a series of them, though we could certainly argue the table is a set of people, so use that.

    Ultimately I’m not sure that it matters much in any particular application. We certainly have databases that using each convention, and probably a few that use both inside the same schema. I think choosing an entity name that is easy to understand is important, and maybe the idea of singular or plural names matters less. After all, as long as you’re not naming tables F42 and H1492, everyone will get used to the convention.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio (3.5 MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • The Relational Database is the Default

    It seems that I regularly meet people in the SQL community that are asked about moving their datastore to a NoSQL type of database. In a mature system, that might be a conversation worth having, especially if there are issues with handling the workload. I doubt there are many type of workloads that I would think are worth moving, but perhaps some exist. However, the discussion about moving is one you can have, given your understanding of the deficiencies you see in your datastore.

    Often I often find is a different story for new applications.  Often I hear about developers becoming enamored with a datastore, a simpler one than SQL Server or some other RDBMS, and look to MongoDB, Cassandra, Hadoop, Neo4J, etc. as the shiny, new, exciting choice. They think NoSQL offers an easier way to build their application, where they can throw and grab JSON through an API, while changing their minds on the ways in which they need to handle data.

    They approach a new application like a startup would, using some exciting technology that has just proven to be successful by some hot company in the news. They look forward to developing an application that other developers will be jealous of next year. Or they just want to use something new that’s being written about on blogs and spoken about at conferences. Or they just want to do something more interesting on the next project. All these might be great approaches, and certainly are ways that make one’s job more engaging.

    However. Just as this piece notes, choosing a new datastore because it’s new and exciting, and not because it fits the data model is a mistake. The data model matters, often because this is the permanent output of your software application. The data will live on and needs to meet current and future needs, separate from the amazing Node.JS framework of the week that your users love. Actually, users don’t love your framework. They love your work. Whether it’s MVC, Angular, Django, or Dapper, most clients could care less. Any language/framework/platform can build an application that works well for clients. Your application just has to work.

    Relational databases work really, really, really well. They handle most problem domains well, with protection against common consistency errors, and they have plenty of features that help you build a solid data model. There are tips and tricks to migrate your schema to some new form if you need to. However, if you need to migrate your schema every few days, you haven’t really thought about the schema. As the article notes, you need a schema. On read, on write, or both, you’re not getting away with ignoring schema.

    And by the way, if you keep your schema in Python or C# or some other language, you’re likely creating a set of “legacy code” that will haunt you for years and is much harder to manage than database schema migrations. Maybe it feels less risky, but it’s probably way less fun to work on.

    Databases are a pain to work with. I get that. It’s so much more cumbersome than refactoring C# classes on the fly. Databases have a different job, and they do it well. If you have a good reason to choose some other datastore that’s related to your problem domain and data model, then go ahead and choose something else. If you don’t, then use SQL Server (or PostgreSQL, Oracle, etc.). Most of you won’t, so just stick with relational databases. You might not thank me, but your organization and future developers will.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 4.8MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • A Database Design Test

    I was watching a presentation recently on database design and the speaker talked about how he hires developers. These are full stack developers, for whom the database is a portion of their responsibility. One of the tasks he gives them is a short database design test, designed to get a rough idea of their knowledge of databases.

    The test is a short story, with a classroom/course/scheduling scenario. There are descriptions in some business terms, and the instructions ask the reader to to decide how to put entities together and link them. There is a sample unlabeled diagram with only a couple boxes. The diagram is meant to clue them in to the way to indicate relationships, and there are names of different structures in the test in bold. For someone experienced in databases, this would seem trivial as the entities are listed in bold, and the test is designed to be completed in 5 minutes. Extra points for not crossing any relationship lines.

    I found this to be a nice, short test to gauge a developer’s knowledge. The speaker noted that they didn’t worry too much about time taken, or the exact notation used in the digram. This is mostly a way to measure if an individual thinks in terms of entities and connections. This is part of a few tests used for a basic evaluation of how a developer solves practical problems, and avoids the trivia based examination used by many interviewers.

    I was intrigued since I’ve never been really asked to design anything and I’ve had quite a few jobs where that would be a portion of my job duties. No one has given me a scenario and asked me to produce an ER diagram. The most I’ve gotten is some theoretical questions on normalization, or what keys are. I wonder if I’m alone. For those of you reading this, have you ever had a design test of any kind in an interview? Can you disclose the types of questions or scenarios? When were you tested? It would be interesting to see if this is used by much of anyone.

    There seem to be so few ER diagrams in the real world, especially from vendors, who should always produce one for clients. I suspect that few people understand them, or even write them, even though they can be invaluable when trying to write reports and understand the relationships between different entities. Many ask, but could those people produce one? Or read it beyond realizing which field in table a connects to which field in table b? Let me know this week if you’ve been tested on your database design skills.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 4.1MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Null Defaults

    One of the things that becomes important in a distributed, team environment is the interface that you present to other systems. In a Devops environment, we really need to have an API for every system, including the database. This means a contract for our database, that spells out what access points are available and what they return. For our data, this often means the structure and shape of a table.

    Our tables often aren’t static. In fact, they grow and chance over time. This means that the values returned from the table, or even views or stored procedures referencing the table, will change over time. In some sense, this means we’re really versioning our API. If that’s the case, then as you make additive changes, you’ll be adding columns for the most part. The will be time when your application, or maybe a subset of your applications, will not know how to provide data for those columns. In those cases, we need to make a decision about how to handle the column.

    What’s your default choice? Do you use NULLs when you aren’t sure? Perhaps you choose some magic value, such as a blanks or a known date? I know some people like to use 1900-01-01 to mean an unknown date, or even a number such as 99999. Any choice has advantages and disadvantages, and what works for one person might not work for another.

    Let us know your default choice and why. I’m sure some of you have great reasons for why you choose a value, and I bet more than a few of us learn something about why one value might be better than others. I’m looking forward to reading your thoughts.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.9MB) podcast or subscribe to the feed at iTunes and Libsyn.