Lately, I have been working with a lot of legacy SQL and this has caused me to think about what makes SQL (and database designs) good and bad.
If you open the ‘Configuration’ tool, you will find ‘enable_cors’ under ‘httpd’. You want to change that value to ‘true’.
Next, if you scroll down to the bottom of the page, you will find an ‘Add a new section’ link. You want to click on that and add an ‘origins’ option with the value of ‘*’ in the ‘cors’ section. You can set the ‘origins’ value to an actual URL but this will enable CORS for everybody.
I would like to give a ‘tip of the hat’ to RichITExperience for having this answer. It looks like nobody else had any good info about this.
So, you just created a CouchDB database, added a user to it, and want access to be limited to that user? There is a simple way of doing this. Let’s use a previous example, for this demo.
As you can see below, by default, you do not need to be authenticated, in order to interact with a database.
You can use the “Configuration” tool, on the right sidebar, to do that. If you look under “couch_httpd_auth”, there is a boolean called “require_valid_user”. By default, the value is “false”. You can change that to “true”.
Once the value is set to “true”, it will require you to do an http authentication for your requests.
This is definitely an unrefined, blunt way of securing your databases. It works, though.
If you are like me, you probably have not had much of a reason to use NoSQL but you have been interested in it. I recently decided to check out CouchDB. CouchDB is an Apache project that was created in 2005 by Damien Katz. CouchDB is interesting in that it stores your data with JSON documents and allows you to query via HTTP. This means that CouchDB provides it’s own web service.
Database normalization is a process meant to reduce or eliminate data redundancy. Typically, a normalized database exists in first normal form (1NF), second normal form (2NF), or third normal form (3NF).
First Normal Form
In order for a database to be in first normal form, your tables must not contain repeating groups of data. Let’s look at a simple example. In the table below, we are tracking basic contact information. We have an ID, a first name, a last name, and the contact’s phone numbers.
So, what wrong with this table? Users can have more than one phone number, so we are storing multiple values within the “PhoneNumbers” column. This means that our DBMS is storing multiple values as a single value and we have to manage the phone number values manually. How can we better this?
In the above example, we placed phone numbers into their own table. We connected the contacts to their phone numbers using the “ContactID” column. Notice that there are no duplicate rows and every row/column intersection contains exactly one value.
Second Normal Form
In order for a database to be in second normal form, it first must satisfy the conditions for first normal form. In addition to that, all of it’s non-key attributes must be fully dependent upon it’s primary key. Let’s look at a table that tracks the things our contacts are experts in.
You will notice in the above table that contacts #2 and #3 are experts in two things. In the case of contact #2, he is an expert in PHP and Digital Design. You will also notice that we keep repeating the facility where the contact is. This was probably placed there to make it easy to find a ColdFusion expert at the Downtown facility. This example table can cause problems because, if contact #2 relocates to the Downtown facility, there is a chance that only one record will be modified, leave contradicting information. So, how do we fix it?
In this new example, the facility is now part of the “Contacts” table. You can still find your ColdFusion expert at the Downtown office but you can avoid update anomalies.
Third Normal Form
In order for a database to be in second normal form, it first must satisfy the conditions for first normal form. In addition to that, the columns should depend solely upon the primary key of the table. What does this mean? Let’s take a look at our “Contact Expertise” table.
Let’s say that we want to see a list of possible skills somebody could be an expert in. This could be so that we can indicate which skill a new contact is an expert in. Right now, we can query “Contact Expertise” for unique values in the “Expertise” column but if we don’t already have an expert in that skill, it won’t be available as an option. We can solve that with a “Skills” table.
Notice that you can now query for possible skills that a contact can specialize in. Additionally, all values are dependent upon the primary key of the table.
How to “cheat” at database normalization
A recent post on the Perl blog had a great set of rules to make sure your database is adequately normalized.
- Every “noun” gets its own table.
- “many to many” relationship get their own tables
- “one to many” relationship require the table that “owns” another table have its ID in that other table
- Any time a table has an ID that refers to a row in another table, use a foreign key constraint to make sure that ID really exists
Remember, your database is the foundation of your application. If you build it wrong, your application will struggle to succeed.