• About Heli

HeliFromFinland

~ Heli's thoughts on Database Designing, Oracle SQL Developer Data Modeler, User Groups etc.

HeliFromFinland

Monthly Archives: July 2015

How to define a PK using Data Modeler?

30 Thursday Jul 2015

Posted by Helifromfinland in Data Modeler, Database design

≈ 7 Comments

Primary Key (PK) identifies uniquely each row in a table. There can never be two tuples with same values in PK.

There are two ways of defining a PK: a natural key or a surrogate key. I would say that if you can find a natural key that is always better. But it is not always possible and therefore we can also use surrogate PKs. Surrogates are usually sequence numbers that has no meaning to the business people, the end users.

Let’s have an example of an entity for all the users in a company.

PK1

PK2

1. Defining a natural PK

What I want is that there will never be more than one user with the same username. If this means that there can NEVER be two with the same username, I would define the PK like this:

PK3

You can see the PK definition from Unique Identifiers tab:

PK4

And by clicking the Properties button (XYZ and a pen) you can see and edit the properties for this unique identifier.

You can for instance change the name

PK5

Or see what attributes or relationships are involved in this PK. You can add or remove attributes or relationships to/from the PK using the arrows pointing to right and left. And you can change the order of the PK elements using the arrows pointing up and down.

PK6

If the requirement would be that there can never be two users at the same time with a same valid username I might think a bit. If I am sure that a pair (Username, Valid) would be enough as a PK, it means that I can only have max two users with the same Username: one with Valid=true and another one with Valid=false, I could define that as the PK, but what happens when the second one needs to be disabled eg. Valid changed to false? Then I would have two tuples with same values in Username and Valid, that would not work because that is my PK. So having a PK with just Username and Valid would not be quite smart. I could also use Created date in PK but would that be smart? No. Having a PK (Username, Valid, Created) would help me to disable a username even though there is already another of the same Username disabled or adding the third user with the same Username but it would not guarantee that only one user would have that Username at a point of time. And that was exactly what the requirement was.

I could add BeginDate and EndDate as attributes:

PK7

But that would not help me because I could not use those attributes in the PK because I should be checking that the two rows are not valid at the same time, eg. comparing the BeginDates and EndDates for all rows having the same Username. Of cause I could check those attributes using PL/SQL and check that only one row is valid at a time but that is not a PK.

No wonder many people end up to the conclusion that the requirement means that there can NEVER be two users with the same Username 🙂 Then the PK (Username) would be sufficient.

But what if the requirement really is that there cannot be more than one at the same point of time? Then I would use surrogate as the PK and use PL/SQL (trigger) for checking the rest.

2. Defining a surrogate PK

There are two ways to define a surrogate PK: manually or automatically.

If I want to create it manually, I simply define an attribute for it and define it as the PK. My recommendation is that if this is the way you want to work, start by defining a Domain for surrogates and always use that when defining a surrogate PK manually. Otherwise defining a surrogate PK manually is done just like I explained earlier on Defining a natural PK.

You can also define a surrogate PK automatically. In entity properties enable Create Surrogate Key and when you engineer to relational model, this surrogate key and the surrogate column are created automatically.

PK8

If using surrogate keys is the preferred way of working for you, you might consider enabling that property so that the default would be Create Surrogate Key enabled.

PK9

Remember that you can define the naming standard for the surrogate key and the column created in Design Properties (right-click on the Design name and select Properties):

PK10

This was a quick look to PKs. A natural key is always the best and being able to define one you must know the requirements and understand them. If a natural key does not work, then a surrogate key is an option.

Kscope15

01 Wednesday Jul 2015

Posted by Helifromfinland in Events

≈ Leave a comment

In June I attended my second KScope and as expected it was an excellent event! This year was even better than last because I had so many new friends I did not have last year 🙂

The Kscope15 was held in Hollywood, Florida June 21-25.

On Sunday I attended the “Database Development for Cloud” session by Jeff Smith and Kris Rice. I also attended the speakers’ reception, welcome reception and a user group leader reception. After all that I was pretty ready to sleep, there it a 7h time difference to my own timezone 🙂

On Monday I attended Tom Kyte’s session about SQL and Chet Justice’s session about “How to Model Your Data”. Then I gave my own session with a title “What’s New with SQL Developer Data Modeler” and gave away two copies of my new book. I had my two sons in the audience and their impression after the session was that I know “all” 😀 Thank you for attending my session! After lunch I gave a couple of interviews. I was told that I am very natural in front of a camera, I could not disagree more 🙂 The General Session was the funnies ever! And next year we will go to Chicago for Kscope16! After the general session it was time for some mojitos in the Exhibition Hall. Then the ACE reception by the pool at one of the hotels near by and the networking events sponsored by OTN. Too bad I do not like beer because Jeff Smith organized an amazing beer tasking Networking Hopportunity event 😦 Well, I still had fun 🙂

On Tuesday I attended Kent Graziano’s “Introduction to Oracle SQL Developer Data Modeler” session and was one of the panelists at the Database Development panel led by Tim Gorman. In the afternoon I attended Michelle Kolbe’s session “Overview of Dimensional Modeling Principles Using Oracle SQL Developer Data Modeler” and Jeff Smith’s “Tuning with Oracle SQL Developer”. Then it was time for some drinks and food and tattoos on Tattoo Tuesday event. Then I had a meeting and a nice evening with friends. Thank you MindStream Analytics for your amazing hospitality!

On Wednesday I had plans to attend presentations by Pete Sharman, Alex Zaballa and Melanie Caffrey but I missed them because I had a busy morning meeting people and then helping Ashley Chen with the Data Modeler Hands-On-Lab. Several people showed up without their laptops so we decided that Ashley will handle the lab and I will do a demo for those without a laptop. I had so much fun! Thank you Ashley for letting me to be part of that! After the HOL I ran to the WIT roundtable where I was moderating one of the tables. Our topic was “Best Qualities of Female Tech Leaders”. I must say I enjoyed the conversation and based on the comments I heard afterwards so did the rest of our table. Thank you for attending and thank you for letting me to be the moderator! In the evening there was a White Party at Nikki Beach. My sons got tickets from Tom and Mel and we did quite a lot of shopping just for the party but just at the last minute we heard the age limit for the party was 21 and doh… No party for us (me and my sons). Instead we went for a nice dinner close to the party venue and early back to the hotel. I was kind of sad because I heard there was a lot of dancing involved the party and I simply love dancing 😦 Well, you cannot always get everything 🙂

On Thursday I slept a bit later and only attended the closing session and then some meetings afterwards.

A busy but wonderful week I must say! Thank you all those wonderful people who made this event happen: Natalie, Monty, Crystal, Lori, Mike, Opal, Sarah, Danny, Martin, Mia, Cameron, Lauren, Melissa and everybody else whose names I just do not remember right now because of my stupid jet lag 😉

It was wonderful to be part of it and it was really great to meet so many friends again! See you next year in Chicago!

Subscribe

  • Entries (RSS)
  • Comments (RSS)

Archives

  • April 2020
  • October 2019
  • October 2018
  • June 2018
  • December 2017
  • October 2017
  • September 2017
  • July 2017
  • April 2017
  • March 2017
  • February 2017
  • January 2017
  • December 2016
  • November 2016
  • October 2016
  • September 2016
  • August 2016
  • July 2016
  • June 2016
  • May 2016
  • April 2016
  • March 2016
  • February 2016
  • December 2015
  • November 2015
  • October 2015
  • September 2015
  • August 2015
  • July 2015
  • June 2015
  • May 2015
  • April 2015
  • March 2015
  • January 2015
  • December 2014
  • November 2014
  • October 2014
  • September 2014
  • August 2014
  • June 2014
  • May 2014
  • April 2014
  • January 2014
  • December 2013
  • November 2013

Categories

  • Data Modeler
  • Database design
  • Events
  • General
  • Oracle
  • SQL Developer

Meta

  • Register
  • Log in

Blog at WordPress.com.

Cancel
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy