Browsing articles tagged with " Database Design"

The Tricky Part of Doing Tricky Things in your Data Model – SQL Saturday Phoenix

Apr 16, 2016   //   by Karen Lopez   //    //  No Comments

SQL Sat PHX logo

We’ve mastered the basics of data modeling and data modeling patterns, but what happens when you or a teammate wants to use this one weird trick to improve on the data model and the resulting database design? Have you just discovered a brilliant new trick that no one else has ever thought of? Or will your new design cost you more, take on more risk and cause all kinds of pain for both IT and your business users?

We’ll look at a few tricks that we’ve seen in our database design review and show you how they work and talk about the trade offs for using them. You’ll learn about:

Building a database engine inside your database
Designing before understanding your business model
Dealing with data structures that don’t do well in RDBMSs
Optimizing the developer versus the data or the app
Using design patterns that don’t reflect modern architectures and tools
…and a lot more

The Key to Keys – SQL Sat Huntington Beach

Apr 2, 2016   //   by Karen Lopez   //    //  No Comments

SQL Saturday HB logo

 

So many of us have learned database design approaches from working with one database or data technology. We may have used only one data modeling or development tool. That means our vocabularies around identifiers and keys tend to be product specific. Do you know the difference between a unique index and a unique key? What about the difference between RI, FK and AK?

These concepts span data activities and it’s important that your team understand each other and where they, their tools and approaches need to support these features.

We’ll look at the generic and proprietary terms for these concepts, as well as where they fit in the database design process. We’ll also look at implementation options in SQL Server and other DBMSs.

The Key to Keys: NTSSUG 17 March

Mar 17, 2016   //   by Karen Lopez   //    //  No Comments

Level: Intermediate

So many of us have learned database design approaches from working with one database or data technology. We may have used only one data modeling or development tool. That means our vocabularies around identifiers and keys tend to be product specific. Do you know the difference between a unique index and a unique key? What about the difference between RI, FK and AK? These concepts span data activities and it’s important that your team understand each other and where they, their tools and approaches need to support these features. We’ll look at the generic and proprietary terms for these concepts, as well as where they fit in the database design process. We’ll also look at implementation options in SQL Server and other DBMSs.

I’ll be speaking about keys: primary keys, surrogate keys, clustered keys, GUIDs, SEQUENCEs, alternate keys…well, there’s a lot to cover about such a simple topic.  The reason I put this presentation together is I see a lot of confusion about these topics. Some of it’s about terminology (“I can’t find anything about alternate keys in SQL Server…what the heck is that, anyway”), some of it is misunderstandings (“what do you mean IDENTITIES aren’t unique! of course they are…they are primary keys!”), some of it is just new (“Why the heck would anyone want to use a SEQUENCE?”).

The Key to Keys at the North Texas SQL Server User Group – 17 March

Mar 15, 2016   //   by Karen Lopez   //   Blog, Data Modeling, Database, Database Design, DLBlog, Speaking, SQL Server  //  No Comments

I’m visiting Dallas this week to speak at the North Texas SQL Server User Group this Thursday.  I’ll be speaking about keys: primary keys, surrogate keys, clustered keys, GUIDs, SEQUENCEs, alternate keys…well, there’s a lot to cover about such a simple topic.  The reason I put this presentation together is I see a lot of confusion about these topics. Some of it’s about terminology (“I can’t find anything about alternate keys in SQL Server…what the heck is that, anyway”), some of it is misunderstandings (“what do you mean IDENTITIES aren’t unique! of course they are…they are primary keys!”), some of it is just new (“Why the heck would anyone want to use a SEQUENCE?”).

We’ll be chatting about all these questions and more on Thursday, 17 March at the Microsoft venue in Irving, Texas starting at 6PM.

Attendance is free, but you need to register at http://northtexas.sqlpass.org/ to help organizers plan for the event.

Don’t worry if you don’t know about SQL Server or don’t use it: this presentation will focus on some SQL Server specific features, but the discussion is completely portable to other DBMSs.

So many of us have learned database design approaches from working with one database or data technology. We may have used only one data modeling or development tool. That means our vocabularies around identifiers and keys tend to be product specific. Do you know the difference between a unique index and a unique key? What about the difference between RI, FK and AK? These concepts span data activities and it’s important that your team understand each other and where they, their tools and approaches need to support these features. We’ll look at the generic and proprietary terms for these concepts, as well as where they fit in the database design process. We’ll also look at implementation options in SQL Server and other DBMSs.

Hope to see you there!

Database Design Throwdown – Austin SQLSaturday

Jan 30, 2016   //   by Karen Lopez   //    //  No Comments

Database Design Throwdown

Speaker(s):  Karen Lopez Thomas LaRock

Duration: 60 minutes

Track: Application & Database Development

Everyone agrees that great database performance starts with a great database design. Unfortunately, not everyone agrees which design options are best. Data architects and DBAs have debated database design best practices for decades. Systems built to handle current workloads are unable to maintain performance as workloads increase.Attend this new and improved session and join the debate about the pros and cons of database design decisions. This debate includes topics such as logical design, data types, primary keys, indexes, refactoring, code-first generators, and even the cloud. Learn about the contentious issues that most affect your end users and how to avoid them.

SQLSaturdays are free to attend, usually with an option to pay for lunch if you’d like.

 

 

 

Database Design Throwdown, Texas Style

Jan 21, 2016   //   by Karen Lopez   //   Blog, Data, Data Modeling, Database, Database Design, DLBlog, Events, Fun, Snark, Speaking, SQL Server  //  3 Comments

SQLSaturday #461 - Austin 2016

It’s a new year and I’ve given Thomas LaRock (@@sqlrockstar | blog ) a few months to recover and ramp up his training since our last Throwdown.  The trophies from all my wins are really cluttering my office and I feel back that Tom has not yet had a chance to claim victory.  So we will battling again in just a few days.

I’ll be dishing out the knowledge along with a handkerchief for Tom to wipe up his tears at SQL Saturday #461 Austin, TX on 30 January 2016.  This full day community-driven event features real database professionals giving free presentations on SQL Server and Data Platform topics.  All you need to do is register (again, it’s free) before all the tickets are gone.

Database Design Throwdown

Speaker(s):  Karen Lopez Thomas LaRock

Duration: 60 minutes

Track: Application & Database Development

Everyone agrees that great database performance starts with a great database design. Unfortunately, not everyone agrees which design options are best. Data architects and DBAs have debated database design best practices for decades. Systems built to handle current workloads are unable to maintain performance as workloads increase.Attend this new and improved session and join the debate about the pros and cons of database design decisions. This debate includes topics such as logical design, data types, primary keys, indexes, refactoring, code-first generators, and even the cloud. Learn about the contentious issues that most affect your end users and how to avoid them.

One of the other great benefits of attending these events is that you get to network with other data professionals who are working on project just like yours…or ones you will likely work on at some point.

Join us an other data pros to talk about data, databases and projects. And make sure you give a #datahug to Tom after the Throwdown. He’s gonna need it.

You’re Doing it Wrong: Generalizations about Generalizations

What Could Go Wrong Complicated Data Model Thumbnail Plus Darth Vader

I have a couple of presentations where I describe how generalized data modeling can offer both benefits and unacceptable costs.  In my Data Modeling Contentious Issues presentation, the one where we vote via sticky notes, we debate the trade-offs of generalization in a data model and database design.  In 5 Classic Data Modeling Mistakes, I talk about over-generalization.

Over the last 20 some years (and there’s more “some” there than ever before), I’ve noticed a trend towards more generalized data models.  The means that instead of having a box for almost every noun in our business, we have concepts that have categories.  Drawing examples from the ARTS Data Model, instead of having entities for:

  • Purchase Order
  • Shipping Notice
  • Receipt
  • Invoice
  • etc

…we have one entity for InventoryControlDocument that has a DocumentType instance of Purchase order, Shipping Notice, Receipt, Invoice, etc.

See what we did there?  We took metadata that was on the diagram as separate boxes and turned them into rows in a table in the database.  This is brilliant, in some form, because it means when the business comes up with a new type of document we don’t have to create a new entity and a new table to represent that new concept.  We just add a row to the DocumentType table and we’re done.  Well, not exactly…we probably still have to update code to process that new type…and maybe add a new user interface for that…and determine what attributes of InventoryControlDocument apply to that document type so that the code can enforce the business rules.

Ah! See what we did there this time?  We moved responsibility for managing data integrity from the data architect to the coders.  Sometimes that’s great and sometimes, well, it just doesn’t happen.

So my primary reason to raise generalization as an issue is that sometimes data architects apply these patterns but don’t bother to apply the governance of those rules to the resulting systems.  Just because you engineered a requirement from a table to a row does not mean it is no longer your responsibility.  I’ve even seen architects become so enamoured with moving the work from their plate to another’s that they have generalized the heck out of everything while leaving the data quality responsibility up to someone else.  That someone else typically is not measured or compensated for data integrity, either.

Sometimes data architects apply these patterns but don’t bother to apply the governance of those rules to the resulting systems

Alec Sharp has written a few blog posts on Generalizations. These posts have some great examples of his 5 Ways to Go Wrong with Generalisation.   I especially like his use of the term literalism since I never seem to get the word specificity out when I’m speaking. I recommend you check out his 5 reasons, since I agree with all of them.

1 – Failure to generalize, a.k.a. literalism

2 – Generalizing too much

3 – Generalizing too soon

4 – Confusing subtypes with roles, states, or other multi-valued characteristics

5 – Applying subtyping to the wrong entity.

By the way, Len Silverston and Paul Agnew talk about levels of generalization in their The Data Model Resource Book, Vol 3: Universal Patterns for Data Modeling book (affiliate link).  Generalization isn’t just a yes/no position.  Every data model structure you architect has a level of generalization.

Every data model structure you architect has a level of generalization.

I’m wondering how many of you who have used a higher level of generalization and what you’ve done to ensure that the metadata you transformed into data still has integrity?

Leave your recommendations in the comments.

Update: I updated the link to Alec’s blog post.  Do head over there to read his points on generalization.

Pages:1234»

Subscribe via E-mail

Use the link below to receive posts via e-mail. Unsubscribe at any time. Subscribe to www.datamodel.com by Email


Recent Comments

Categories

Archive

UA-52726617-1