Tuesday, July 1, 2008
Lookup Tables
Also known as reference, crosswalk and a few other names.
I love 'em.
I'm not afraid to use them. It certainly makes that table count go up, but you know what you're getting.
I have ADDRESS_TYPES, PHONE_TYPES, PERSON_TYPES (in an intersection table of course) and any other kind of TYPE you can imagine.
I could use CHECK constraints I guess, but if it's anything other than Y or N, I typically create a lookup table to go with the table.
Let's take an ADDRESS table. ADDRESSTYPECODE becomes an attribute of an address. It gets a Foreign Key to the ADDRESS_TYPES table and also (many seem to leave this one out), a NOT NULL constraint. Every address has to have an type.
To make it somewhat easier, I use codes (as opposed to IDs which I tend to associate with numbers) so a join isn't absolutely necessary. If 'HOME' is the ADDRESSTYPECODE, you would rarely need to join as it's self evident what that means. If the lookup table is large, I'd typically use ID (or numbers) for the key.
Like I said, it bumps up that table count and makes things look a bit "messy," but you know exactly what belongs in what column. And if you're using ApEx, administrative screens are a snap!
Just don't ask Duke Ganote whether type is a good name or not!
I love 'em.
I'm not afraid to use them. It certainly makes that table count go up, but you know what you're getting.
I have ADDRESS_TYPES, PHONE_TYPES, PERSON_TYPES (in an intersection table of course) and any other kind of TYPE you can imagine.
I could use CHECK constraints I guess, but if it's anything other than Y or N, I typically create a lookup table to go with the table.
Let's take an ADDRESS table. ADDRESSTYPECODE becomes an attribute of an address. It gets a Foreign Key to the ADDRESS_TYPES table and also (many seem to leave this one out), a NOT NULL constraint. Every address has to have an type.
To make it somewhat easier, I use codes (as opposed to IDs which I tend to associate with numbers) so a join isn't absolutely necessary. If 'HOME' is the ADDRESSTYPECODE, you would rarely need to join as it's self evident what that means. If the lookup table is large, I'd typically use ID (or numbers) for the key.
Like I said, it bumps up that table count and makes things look a bit "messy," but you know exactly what belongs in what column. And if you're using ApEx, administrative screens are a snap!
Just don't ask Duke Ganote whether type is a good name or not!
Labels: apex, constraints, database, design, development
¶ 7/01/2008 09:52:00 PM 3 Comments Links to this post
Thursday, March 27, 2008
How Does Oracle Make Development Easier?
Continuing on the theme of late, what are the basic things that you can do to reduce the amount of code that needs to be written?
In the post linked above, I mentioned Constraints as probably the easiest way to reduce the amount of coding. For example:
FIRST_NAME - is not optional you hence the NOT NULL constraint.
MIDDLE_NAME - is optional (no constraint).
LAST_NAME - is not optional (NOT NULL).
GENDER - is not optional (NOT NULL). Also, you want to exclude everything but 'M' or 'F', thus the CHECK constraint.
SSN - is not optional (NOT NULL). The length of the value must be 9 characters (CHECK). The characters may only be numeric (CHECK). Unfortunately I don't yet know the REGEXP_INSTR function yet to truly demonstrate. Finally, the UNIQUE constraint on SSN since they shouldn't duplicate across people.
This is a simple demonstration of how you can potentially use constraints to reduce the amount of code necessary. Though I would probably check/validate these as well in code because the error that is generated will not be unique so it would difficult to tell. The point is, if you make a mistake in your validation code it will be easily caught by the constraints forcing you to fix it.
This will give you much more reliable data, which as we all know, is the most important thing.
I'd like to do more of the posts pointing out the easiest methods to reduce the amount of code you have to write by using Oracle.
What kind of solutions do you have or do you use?
In the post linked above, I mentioned Constraints as probably the easiest way to reduce the amount of coding. For example:
ID - is just a sequence generated key, no big deal there.
CREATE TABLE t
(
id NUMBER(10)
CONSTRAINT pk_id_t PRIMARY KEY,
first_name VARCHAR2(30)
CONSTRAINT nn_firstname_t NOT NULL,
middle_name VARCHAR2(30),
last_name VARCHAR2(40)
CONSTRAINT nn_lastname_t NOT NULL,
gender VARCHAR2(1)
CONSTRAINT nn_gender_t NOT NULL
CONSTRAINT ck_morf_gender_t CHECK ( gender IN ( 'M', 'F' ) ),
ssn VARCHAR2(9)
CONSTRAINT nn_ssn_t NOT NULL
CONSTRAINT ck_9_ssn_t CHECK ( LENGTH( ssn ) = 9 )
CONSTRAINT ck_numeric_ssn_t CHECK ( REGEXP_INSTR( ssn, ?, ?, ? ) )
CONSTRAINT uq_ssn_t UNIQUE
);
FIRST_NAME - is not optional you hence the NOT NULL constraint.
MIDDLE_NAME - is optional (no constraint).
LAST_NAME - is not optional (NOT NULL).
GENDER - is not optional (NOT NULL). Also, you want to exclude everything but 'M' or 'F', thus the CHECK constraint.
SSN - is not optional (NOT NULL). The length of the value must be 9 characters (CHECK). The characters may only be numeric (CHECK). Unfortunately I don't yet know the REGEXP_INSTR function yet to truly demonstrate. Finally, the UNIQUE constraint on SSN since they shouldn't duplicate across people.
This is a simple demonstration of how you can potentially use constraints to reduce the amount of code necessary. Though I would probably check/validate these as well in code because the error that is generated will not be unique so it would difficult to tell. The point is, if you make a mistake in your validation code it will be easily caught by the constraints forcing you to fix it.
This will give you much more reliable data, which as we all know, is the most important thing.
I'd like to do more of the posts pointing out the easiest methods to reduce the amount of code you have to write by using Oracle.
What kind of solutions do you have or do you use?
Labels: code, constraints, database, oracle, simplicity
¶ 3/27/2008 10:28:00 PM 0 Comments Links to this post
Tuesday, March 25, 2008
Use the [Oracle] Database dammit!
Dom Brooks recently posted an article about the Dea(r)th of the Oracle RDBMS. It seemed to struck a chord.
I've written about MySQL Friday or Application Developers vs. Database Developerswhich were similar in thought; the database is a bucket.
Ultimately, my take is that application developers don't know and don't want to learn how to use a database. PL/SQL specifically, is a platform in and of itself. You can do so much in the database now that you essentially need an application only for display, to determine the row color if you will.
The usual caveat follows:
If you are building applications that are supposed to be database independent, then the logic belongs in the application. The database is a bucket.
If you are building business applications specific to Oracle though, use the damn thing. Application/web developers are then forced to work on the design and user interface, not application/transaction logic.
Easy steps to actually utilize your database:
1. Use as many constraints as humanly possible - This will reduce the amount of code you have to write and you'll have the security of knowing the data will be what you constrain it to be.
2. DEFAULT columns in table definitions - create_date or load_date can be default to SYSDATE and thus left out of any application code. I've gone so far as to use SYS_CONTEXT( 'MY_CONTEXT', 'USERID' ) as the DEFAULT value for the create_user column. That along with a NOT NULL (or CHECK) constraint, makes life that much easier.
3. Did I mention constraints? Primary Key and Foreign Key constraints are very important to maintain data integrity (ensure you have the data you expect). Don't forget to index those foreign keys.
4. Security - VPD (Virtual Private Database) or Fine Grained Access Control. No longer do you need to maintain two separate schemas (or databases), just add a column and only allow those with the value set see that data. If you are using ApEx, this is incredibly easy to do.
5. Security (Roles and Privileges) - No more table based authorization, let the database do it through roles and privileges. GRANT EXECUTE ON my_package TO some_user
That's my short list for today. Like Dom, this makes me angry. If there were some rational logic behind it, great, convince me. I haven't seen it yet though.
I've written about MySQL Friday or Application Developers vs. Database Developerswhich were similar in thought; the database is a bucket.
Ultimately, my take is that application developers don't know and don't want to learn how to use a database. PL/SQL specifically, is a platform in and of itself. You can do so much in the database now that you essentially need an application only for display, to determine the row color if you will.
The usual caveat follows:
If you are building applications that are supposed to be database independent, then the logic belongs in the application. The database is a bucket.
If you are building business applications specific to Oracle though, use the damn thing. Application/web developers are then forced to work on the design and user interface, not application/transaction logic.
Easy steps to actually utilize your database:
1. Use as many constraints as humanly possible - This will reduce the amount of code you have to write and you'll have the security of knowing the data will be what you constrain it to be.
2. DEFAULT columns in table definitions - create_date or load_date can be default to SYSDATE and thus left out of any application code. I've gone so far as to use SYS_CONTEXT( 'MY_CONTEXT', 'USERID' ) as the DEFAULT value for the create_user column. That along with a NOT NULL (or CHECK) constraint, makes life that much easier.
3. Did I mention constraints? Primary Key and Foreign Key constraints are very important to maintain data integrity (ensure you have the data you expect). Don't forget to index those foreign keys.
4. Security - VPD (Virtual Private Database) or Fine Grained Access Control. No longer do you need to maintain two separate schemas (or databases), just add a column and only allow those with the value set see that data. If you are using ApEx, this is incredibly easy to do.
5. Security (Roles and Privileges) - No more table based authorization, let the database do it through roles and privileges. GRANT EXECUTE ON my_package TO some_user
That's my short list for today. Like Dom, this makes me angry. If there were some rational logic behind it, great, convince me. I haven't seen it yet though.
Labels: constraints, database, development, oracle, work
¶ 3/25/2008 03:16:00 PM 2 Comments Links to this post
Thursday, October 11, 2007
Code Style: Tables
Tables are easy.
For the datawarehouse, you'll need to think about constraints a bit more as it may slow down load times. I'm still all for constraints, but I would never say always use them.
For child tables:
This would be helpful if someone up and decided to change the NUMBER(10,0) to a VARCHAR2(10) or something (please don't ever do that!).
As for STORAGE or other table options, I typically leave that up to the DBA or work with them to add them. They may have a particular setup for certain tables that you can't possibly know (if you don't talk to them).
To recap:
Remember to always name your constraints. While I am at, use constraints as much as humanly possible, at least in your OLTP systems. You'll be able to reduce the amount of code you need to write and actually let the database do it's job. I'd much rather let the database do it than rely on code to maintain my data integrity.
CREATE TABLE t
(
col1 NUMBER(10,0)
CONSTRAINT pk_col1 PRIMARY KEY,
col2 VARCHAR2(32)
CONSTRAINT nn_col2_t NOT NULL
CONSTRAINT uq_col2_t UNIQUE,
col3 VARCHAR2(400),
col4 VARCHAR2(1) DEFAULT 'N'
CONSTRAINT ck_yorn_col4_t CHECK ( col4 IN ( 'Y', 'N' ) )
CONSTRAINT nn_col4_t NOT NULL
);
For the datawarehouse, you'll need to think about constraints a bit more as it may slow down load times. I'm still all for constraints, but I would never say always use them.
For child tables:
For Foreign Key constraints, you do not have to declare the type as it will be inherited from the parent table.
CREATE TABLE s
(
col5 NUMBER(10,0)
CONSTRAINT pk_col5 PRIMARY KEY,
col1
CONSTRAINT fk_col1_s REFERENCES t( col1 )
CONSTRAINT nn_col1_s NOT NULL,
col6 VARCHAR2(30)
);
This would be helpful if someone up and decided to change the NUMBER(10,0) to a VARCHAR2(10) or something (please don't ever do that!).
As for STORAGE or other table options, I typically leave that up to the DBA or work with them to add them. They may have a particular setup for certain tables that you can't possibly know (if you don't talk to them).
To recap:
- Use constraints as much as possible
- Always name your constraints
- Work with your DBA for table options
- Always name your constraints
Labels: code, constraints, development, discipline, style
¶ 10/11/2007 12:37:00 AM 0 Comments Links to this post
Tuesday, September 11, 2007
Inline vs. Out-of-line Constraints?
Today one of my more feisty colleages and I had a discussion about constraints. I had asked him if he wouldn’t mind naming the NOT NULL constraints to a particular table definition. With this guy, nothing is easy. ;-)
So he came over and we talked about inline vs. out-of-line constraints. I asked him the advantage of out-of-line. He asked me the advantage of inline. I’m sure his answer was better than mine as he is much more articulate.
For me, it’s mostly a style thing, except for FOREIGN KEY constraints which, if defined inline, will inherit the data type of the parent column. That makes life easier if you ever need to change the data type of a parent key (not a recommended best practice mind you). I just think it looks prettier (factual based evidence). It’s all in one file and I like to see how many constraints I can put on a single table. I’m shooting for at least one per column to save myself time down the road of coding exceptions. Let the database do it’s job!
Here’s a sample of inline constraints:
And out-of-line Constraints:
So, what are the advantages for either method? I couldn’t find much via google or asktom, but I probably just didn’t search on the right terms. I find it hard to believe that this topic hasn’t come up before.
* Note that I did use 5 constraint types
So he came over and we talked about inline vs. out-of-line constraints. I asked him the advantage of out-of-line. He asked me the advantage of inline. I’m sure his answer was better than mine as he is much more articulate.
For me, it’s mostly a style thing, except for FOREIGN KEY constraints which, if defined inline, will inherit the data type of the parent column. That makes life easier if you ever need to change the data type of a parent key (not a recommended best practice mind you). I just think it looks prettier (factual based evidence). It’s all in one file and I like to see how many constraints I can put on a single table. I’m shooting for at least one per column to save myself time down the road of coding exceptions. Let the database do it’s job!
Here’s a sample of inline constraints:
DROP TABLE s;
DROP TABLE t;
CREATE TABLE t
(
id NUMBER(10)
CONSTRAINT pk_id PRIMARY KEY,
name VARCHAR2(30)
CONSTRAINT nn_name_t NOT NULL,
age NUMBER(2,0)
CONSTRAINT nn_age_t NOT NULL
CONSTRAINT ck_gtzero_age_t CHECK ( age >= 0 )
);
CREATE TABLE s
(
sid NUMBER(10)
CONSTRAINT pk_sid PRIMARY KEY,
id
CONSTRAINT fk_id_s REFERENCES t ( id ),
something_unique VARCHAR2(30)
CONSTRAINT uq_somethingunique_s UNIQUE
);
And out-of-line Constraints:
DROP TABLE s;
DROP TABLE t;
CREATE TABLE t
(
id NUMBER(10),
name VARCHAR2(30),
age NUMBER(2,0)
);
ALTER TABLE t ADD CONSTRAINT pk_id PRIMARY KEY ( id );
ALTER TABLE t ADD CONSTRAINT nn_name_t CHECK ( name IS NOT NULL );
ALTER TABLE t ADD CONSTRAINT nn_age_t CHECK ( age IS NOT NULL );
ALTER TABLE t ADD CONSTRAINT ck_gtzero_age_t CHECK ( age >= 0 );
CREATE TABLE s
(
sid NUMBER(10),
id NUMBER(10),
something_unique VARCHAR2(30)
);
ALTER TABLE s ADD CONSTRAINT pk_sid PRIMARY KEY ( sid );
ALTER TABLE s
ADD CONSTRAINT fk_id_s FOREIGN KEY ( id ) REFERENCES t ( id );
ALTER TABLE s
ADD CONSTRAINT uq_somethingunique_s UNIQUE ( something_unique );
So, what are the advantages for either method? I couldn’t find much via google or asktom, but I probably just didn’t search on the right terms. I find it hard to believe that this topic hasn’t come up before.
* Note that I did use 5 constraint types
Labels: constraints, database
¶ 9/11/2007 10:19:00 PM 3 Comments Links to this post
