- “The Cloud will cost x” (per user/month), but how much does my server actually cost me?
- For that matter, how much is all that data worth (to me)?
- For many situations a constraint is redundant. The fundamental structure of many applications means they are unlikely to create orphan rows.
- The cost of the constraint is in the extra processing it causes during update operations. This cost is incurred every time a value in the constrained column is updated.
- The benefit of a constraint is that it absolutely protects the constrained column from rogue values. This may be particularly relevant if the system has components (such as load utilities or interfaces with other systems) which by-pass the normal business transactions.
- Other benefits of constraints are that they unequivocally state the “intention” of a relationship between tables and they allow diagramming tools which navigate the relationships to “do their thing”. Constraints provide good documentation, which is securely integrated with the database itself.
- The costs of constraints are small, but constant and in the immediate term.
- The benefits of constraints are avoiding a potentially large cost, but all in the future.
More Detailed Argument
- we create or update the ORDER and ORDER_LINE entities, and
- in addition we refer to PRODUCT (to get availability and Price) and presumably to the CUSTOMER entity which isn’t shown on the diagram.
What’s the problem?
- Preparation: Collect any documentation
- “Brainstorm:” Try to guess the tables/entities you will find in the database.
- List the actual Tables
- Group the Tables: based on name
- For each “chunk”, do the following:
- Identify “keys” for tables: Look for Unique indexes.
- Identify candidate relationships: Based on attribute names, and non-unique indexes.
- Draw your relationships.
- “Push out” any tables that don’t fit.
- Move on to the next group.
- When you’ve done all the groups, look for relationships from a table in one group to a table in another.
- Now try and bring in tables that were “pushed out”, or are in the “Miscellaneous” bucket.
- Repeat until you have accounted for all the tables.
The method stages in more detail.
List the tables:
Group the tables
Identify the candidate keys, and foreign keys from the indexes
From attribute names and indexes, identify relationships
- Sometimes the index names are a give-way (FK_…)
- Sometimes you have to look for similar column names (ORDER_LINE.ORDER_ID à ORDER.ID)
- Multi-part indexes sometimes indicate hierarchies.
Look for “Inter.group” Relationships
My personal development time last week was spent completing an online course “Oracle DBA for absolute beginners”.
I wouldn’t have described myself as an “absolute beginner”, but I found plenty to enjoy in the course and came away having learned quite a bit about what is going on inside Oracle, and I assume most other database managers.
Circumstances influence what we do in life and so far I have had much more exposure to DB/2 and MS SQL Server than to Oracle. That hasn’t been a decision on my part, simply the choices that had been made for the projects I was involved in.
In a similar way, I’ve spent much more time “dealing with users” as a Business Analyst, than I have working out how to manage the space requirements and performance of a database. It does me good to learn just a little about the things a DBA has to consider. I don’t have to let those considerations govern what I consider the requirements to be, but at least I can understand where other people are coming from.
Taking the course led me to what you might consider “meta” thinking: thinking about not the content of the course, but the way it was presented and the platform Udemy on which it was presented.
I find Udemy interesting. It seems to work well. It certainly worked for me.
Udemy seem to be aiming to be a “neutral marketplace”. The courses belong to the course instructors. Of course Udemy have standards for courses, but beyond the usual “fit to print” conditions, they are mostly technical standards (quality of video and sound) rather than subject matter related. In a similar spirit, Udemy promote the platform, but the promotion I have seen seems to be fairly neutral with regard to individual courses. On the other hand, instructors or course owners are completely free to advertise their wares elsewhere and direct potential customers into Udemy. It’s a simple model which I think I will investigate further.