Showing posts with label Primary Key. Show all posts
Showing posts with label Primary Key. Show all posts

Friday, July 25, 2014

SQL Server single row table

In SQL Server 2008 R2 I made a single row table that had Application Settings.

In order to enforce a table constraint of one row, create the table with a PK constraint on and id column with a CHECK contraint of idCol = 1. This will do two things, it will enforce that every row is unique and not null with the PK and the CHECK will ensure that the only val possible is 1.

Then, all you have to do is insert a row with id 1 and all other columns are setting vals (global settings for the app). If you need multiple settings, join tables to this one.

For Multi-tenant applications, consider adding a table for tennant specific settings. This table is for global settings only.

Saturday, July 5, 2014

Primary Key: ID or Combo Key?

Debating a coworker about a table schema in SQL server. The debate is about a parent child relationship. Should the child have its own identity?

Here is the situation (in a generic way).

PeopleTable (a proxy of a table in another db)
- PersonID PK

PlanYearTable
-PlanYear PK

SpecificPlanPersonYearDetailsTable
-PersonID FK PK
-PlanYear FK PK
-other details columns

I am of the mind that the third table should have its own ID rather than the combination PK that is also an FK from two other tables. Even though there will only be one instance per person, per year.

Here's my rationale -
the specific plan is its own entity, even if it can only relate to the specific person and year. SQL Server needs to work harder to order by two columns also to join on two columns (as so users running queries). And if you ever want to create a rest API, what does the URL look like?

The other side of the argument is rooted in difficulties encountered with a database that created its own local ID for referencing entities from another db.

DB Main

BucketTable
-bucketId PK

DropletTable
-dropletId PK
-bucketId FK PK

DB Two

BucketsTable
-bucketId PK

DropletsTable
-LocalDropletId PK
-RemoteDropletId
-bucketId FK

this was done because the droplets in main are partitioned per bucket
Bucket 1 - Droplet 1,2,3.
Bucket 2 - Droplet 1,2,3.
so that there is no way to refer to a droplet without referring to the bucket as well. So this schema was born of another schema and was an attempt to simplify referring to a single droplet entity in the db.

there are other structures that can make our database more open to future change. EAV for the plans would be a great option! Plans change, new ones are added. EAV would handle this beautifully.

PlanEntities
-ID
-Type

PlanAttributeTypes
-ID

ValueTypes
ID

PlanAttributeValues
-ID
-PlanEntityID
-AttributeID
-ValueTypeID
-Value

Of course in this case the plans will not change that often or by that much, so EAV is un-necessary since it adds complexity.

In any case, my preference is to have an ID for each table. The PersonID and Year are still necessary and can be used for identifying the relationship. But in this way the plan has its own identity. Other entities relates to the plan itself can refer to the plan directly.
Guess we will see how it goes.