Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

An address can belong to either the mailing or billing categories.

When a user adds an address to a table, should the address category be an independent integer value set by a constant somewhere in the source code, or should it be a foreign key to a table that lists the categories available?

I'm concerned about performance and best practice.

The chances of adding or removing categories are slim to none.

share|improve this question
1  
We thought that too, turns out some places have a shipping address which is different from mail delivery and billing as well as a physical location. –  billinkc Sep 25 '12 at 17:18

2 Answers 2

up vote 6 down vote accepted

Performance is an excellent thing to consider but I find it helpful to worry about other costs like data quality. Referential integrity ensures that no values would be assigned that don't have a match in the list of values table. Everyone knows 1 is mailing, 2 is billing, piece of pie. FNG comes on board and makes a code change and oops, we now have addresses with an address type of 12. And we've had that problem for 2 months now, customers are angry that they have not received their product and they're filling complaints with the BBB. RI would have caused the app to blow up when they tried to push those bad values into the database.

"Nobody's going to make that mistake here." Fine, the app is a success and brings in lots of money. Eventually someone's going to want to know more about what's going on. We want reports and yes, we also said we need all these new features done by last week. So now everywhere where you've embedded that knowledge that 1 is billing and 2 is mailing you need to repeat in the reporting thing (Excel, traditional reporting systems, ad-hoc queries, etc) you give to the end users to make them happy. Now you've got that logic in two places so any changes to that logic has to be propagated across two systems.

Each time you carry logic like that across systems just adds to your technical debt. Maybe your system is never going to grow like that and it's fine to embed the logic in the app code but I've found the quickest way to achieve immortality is to implement a quick fix.

Oh and did you notice I switched up the values in the report example? The FNG who you brought in could make a rookie mistake like that and who knows how long it's going to take to catch the mistake.

share|improve this answer
3  
You could equally well enforce this via a check constraint as an FK constraint though. The second table approach tends to be more convenient for querying and updating IMO but not everyone is a fan –  Martin Smith Sep 25 '12 at 17:34
    
Yes, excellent point. A constraint would serve to address the risk of invalid values being added to the table but as you say they are less convenient for querying. Thanks for the feedback, I'll try to amend my answer this evening to consider constraints –  billinkc Sep 25 '12 at 19:15

I'd suggest you store them in the database. If you have a CATEGORIES table with a catergory_id, category_name, and category_description, it makes changing and maintaining the data easier. It gives you the ability to have queries that include the actual category name such as "billing", instead of having to write code somewhere else that does if $cat=1 then "billing"...

I can't think of any ways this will have a negative impact on performance (it's one extra table whose size is probably negligible when compared to the other tables you have), and it's a very common practice to do this.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.