Tag: design patterns

The Keys & Relationships of Relational Databases

This is a continuation of my posts on relational databases, started here. Previous posts on this theme, “Designing a Relational Database – Data Modeling, “Let’s Talk About Database Schema“, and “The Exasperating Topic of Database Indexes“.

In the context of relational databases, “keys” and “relationships” are fundamental concepts that help in ensuring data integrity, organizing data efficiently, and enabling meaningful data retrieval. Let’s break these down:

1. Keys

A key is an attribute (or a set of attributes) that uniquely identifies a record in a table. Various types of keys exist in relational databases:

  • Primary Key: This uniquely identifies each record in a table. A primary key cannot have NULL values, and each value must be unique. A table can have only one primary key, which may consist of single or multiple columns.
  • Foreign Key: This is an attribute or set of attributes in a table that refers to the primary key in another table. It establishes a relationship between two tables. A foreign key ensures records in one table correspond to records in another.
  • Unique Key: Similar to a primary key, it uniquely identifies each record in the table. The difference is that a table can have more than one unique key, but only one primary key. Unique keys can have NULL values, but the values must be unique.
  • Composite Key: When more than one attribute is used to uniquely identify a record in the table, such a key is called a composite key.
  • Candidate Key: Any attribute or set of attributes that could serve as the primary key is a candidate key. It means it can uniquely identify records in the table.
  • Super Key: It is a set of attributes that, when taken collectively, can be used to uniquely identify records. A super key can have additional attributes that are not strictly necessary for unique identification.

2. Relationships

Relationships in relational databases determine how tables connect to one another and how data correlates:

  • One-to-One (1:1) Relationship: In this relationship, one record in a table is related to one and only one record in another table. For instance, each employee in a company might have one unique work ID.
  • One-to-Many (1:N) or Many-to-One (N:1) Relationship: One record in a table can be related to one or more records in another table. For example, one customer can place many orders, but each order is made by one customer.
  • Many-to-Many (M:N) Relationship: Multiple records in one table are related to multiple records in another table. For instance, students and courses. One student can enroll in many courses, and one course can have many students. This relationship is typically resolved in relational databases using a junction table (or bridge table).

Relationships are typically enforced using Foreign Keys. When a foreign key in one table refers to the primary key of another, it creates a linkage that ensures the validity of the data and maintains referential integrity.

Continue reading “The Keys & Relationships of Relational Databases” →

Apress and O’reilly

So I had a miniature epiphany today while coding away on a multi-tier application I’m building for interactions with one of my other blogs and with a database driven website application.

That epiphany is that I have two books, the have basically outlined the difference between a $70,000 developer and a $105,000 developer or architect.

The first book is Pro ASP.NET 2.0 in C# 2005 from Apress.  If a developer knows all the ins and outs of each piece of technology in this book they are easily worth 65-75k.  Probably a little bit more depending on their location, but definately NOT less.

The second book is Head First Design Patterns from O’Reilly Press.  This book is not for the beginning developer really as the design patterns, any design patterns, aren’t really of any use until after one understand object oriented programming, basic project management, and various other topics at different levels of software development.  A developer that knows at least 1/2 of what this book offers up is easily worth $105,000 or more depending on position as developer or architect.  A developer with this knowledge would probably top out at about $90-95,000 with this knowledge, but tag Senior Developer, Architect, or Senior Architect and that range is easily somewhere in the $100-125,000 yearly range.

So to those budding developers, if you want a gauge of where you are, hit up these two books and you’ll have a good idea of where you stand.  If you’re under that bracket and you live somewhere out on the left coast (LA, San Fran, Portland, or Seattle) you REALLY better renegotiate or find a new job.  You’re bringing the rate down for the rest of us.  Even India has brought it’s rate up that the flow of offshoring has slowed to a trickle as of late.

As for developers out in the midwest or southern US, I’d estimate these ranges accordingly with cost of living, usually about 15-30% less than what is above… but then of course you guys in those areas can buy 3,000 square foot houses on 2 acres of land for the price of a 700 square foot condo out on the left.  🙁

anyway… enough of my rambling off topic discussions.