Voice of the DBA

Tag: database design

  • Logging Tables

    I was chatting in one of our DLM webinars and someone asked a question about logging data. I mentioned a few things, but this individual followed up and wanted more information. Specifically, how do you build a logging table?

    It’s an open ended question, but it’s perfect for a poll. How do you construct a table that you use for capturing lots of information that you may or may not query later, but you might only query in a very ad hoc fashion when something has gone wrong. Usually that’s the only time I look through logs, when I’m investigating some type of issue. With that in mind, this week the question is:

    How do you design a logging table?

    I’m looking for two things here: the DDL and the rational for the design. What is good about this design? How does this particular structure (with indexing) allow me to find answers to the questions I might need to ask.

    I realize this is very open ended, but the concept of logging is also open-ended, used different ways for different purposes. If you have problems with logging structures, let us know what design techniques to avoid as well.

    Steve Jones

    The Voice of the DBA Podcast

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

  • ER Modeling

    I saw someone recently note that an Entity-Relationship model is important for your database development project. That UML is important, and the model should be actively maintained. I agree.

    However, I’ve usually not seen this done. Typically when I’ve seen an ER diagram in a shop, it resides on a wall, printed out on multiple sheets of paper. Often there are as many handwritten additions on the pages as there are marks from a printer. The diagram is also usually out of date, even allowing for handwritten notes.

    Maintaining a diagram is hard. Deciding to actually spend time working on one instead of just talking about a few entities and building them, may be harder. This week, I wanted to ask you if you’re up to the challenge in your organization.

    How many of you maintain an ER model for your database?

    Do you have an ER diagram you’ve built? Whether it’s in ErWin, Visio, or Paint. Is there a diagram that’s actively maintained and that everyone can use for designing changes to the database(s) in use? Do people actually consult this and use it in design meetings? Let us know this week.

    Steve Jones

    The Voice of the DBA Podcast

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

  • No More Foreign Keys?

    I struggle with removing FKs as a general idea. It’s presented in a piece from a developer. It appears to me that if you do this, you are merely moving the problem, and potentially causing other issues. Apart from Grant’s response on performance, which is very true, there are other challenges with microservices and separate databases for each service.
    Let’s say we are doing something trivial, like a simple e-commerce site. I could have a separate database for products, one for orders, one for shipping actions, one for customers, etc. However as I scale, I’m not using intra-database queries or FKs/joins/other techniques to verify information for an order. Now I’m sending messages. At small scale, these are trivial. At large scale, my network traffic and messaging is vastly growing and potentially a bottleneck.
    Also, since there is a “products” database, and an “orders” database and a “customers” database, I still can’t necessarily scale out these microservices beyond their own machine. Perhaps I can scale them higher in terms of each being as large a machine as I can have now with a single Oracle/SQL Server/MySQL/etc. box, but I’m still have a scaling issue. I also now have a new messaging problem I need to manage and architect. If I lose one database, how gracefully can my application degrade or does it start to fail with unforeseen interactions? Do I create more frustration when customers cannot place an order because we can’t find the customer db or because the site is down?
    Certainly there are domains of problems that would work fine here. Spotify is probably a good example of this. There may be e-commerce systems that allow this, perhaps being willing to notify customers after some time (hopefully minutes, perhaps hours) that a product is out of stock because the message from the orders database didn’t get to the application. There are certainly ways to copy and replicate some  data, assuming that “fresh enough” works for your application. However that’s a business decision more than a coding decision. This also means that you also have a new load on your system to move data. I also think the more you work with singleton rows and single objects in an application context, the less you need RDBMS capabilities.
    Ultimately I think FKs and strong RDBMS systems work very well in many situations. They work less well in many situations and your domain may qualify for those areas where a NoSQL, a multi-db, or other architecture works. We should certainly investigate and write about where things work and don’t work, but I wouldn’t assume the RDBMS with it’s PKs and FKs isn’t a valid, well thought out and incredibly useful architecture.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Making Guesses

    I was reading a bit about the design and planning stage for a new database that will support some random application. The piece isn’t important, but I did notice one thing. The guidelines are generic, encompassing most of the things we might want to think about: structure of data, constraints, volumes of data and rate of growth, and more. It’s a good list, and one many of you have probably considered at some point if you’ve designed a system.

    However, I’ve rarely been able to actually do more than guess at many of the answers. Even much of the data I can write down is often little more than a guess. Even when I know the DRI constraints, I hesitate to document them separately from the code itself. Mostly because I find designs change early and often, and the documentation is rarely able to keep up with code. The same thing applies with the administrative type decisions I make.

    I’m also nervous about documenting guesses.   I find that far too often any answer I give takes on some level of truth with the developers, often being considered an immutable fact. The assumption that an early guess will match reality later seems to often blind everyone and limits the level to which we can adapt to changing situations.

    As an example, if I tell a SAN admin that we think we need 5GB of space, but we find that we need 20GB in a short period of time, I meet resistance. A debate, or argument ensues, we search for evidence that we need to change, and a very inefficient process ensues. The same thing occurs when developers, security admins, and especially managers hold too tightly to estimates and guesses.

    Certainly some guidelines are needed, but when I’m not sure, I tend to give wide ranges, just to prevent too many expectations from taking hold in people’s minds. We should know that our plans will rarely survive production deployments and workloads, but somehow we forget that when looking at the guesses we make early on.

    Steve Jones

    The Voice of the DBA Podcast

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