June 21, 2010 at 9:49 am
Let's say I have two tables - a Persons table and an Addresses table. One person can have zero to many addresses. Each address record has a bit column named "IsPrimary."
How can I ensure that each row in the Persons table that has more than one row in the Addresses table has only one "primary" address at a time?
Is this something that should be handled in the business layer of an application, rather than in the database, or can the database handle this?
I don't think a check constraint would work for this, but I may be wrong. If so, please enlighten me.
For instance, person A has one address, which is marked as the "primary" address. A new address record is subsequently added for person A, and it now needs to be the primary address. A check constraint would not allow the new record to be added until the "IsPrimary" column in the first record is set to false, correct? If so, how is this accomplished, short of using a stored procedure in combination with the check constraint?
I'm looking for best practices here, or an established pattern. I'm sure this is a fairly common scenario, right? How do other database architects handle this sort of thing?
Thanks for any input you can share.
June 21, 2010 at 10:34 am
A constraint on the database can ensure that a second primary record isn't added outside the application. I think the application should handle the insert/update to ensure that only one record is coded as the primary address record. This coul dbe handled in several differnet ways, depends on how you want to do it.
June 21, 2010 at 11:02 am
You could use a trigger to prevent this, but I'm with Lynn. I'd think the application should handle this. I'd also set up an exception job that ran nightly to look for issues and alert someone to go check. Ultimately if you have few applications, this shouldn't be run often, and likely will work.
June 22, 2010 at 7:35 am
Thanks for the responses. You pretty much confirmed my suspicions, that this sort of thing needs to be handled in the business logic of the application.
June 22, 2010 at 8:02 am
You could add PrimaryAddressId column into your Person's table, that would ensure the person has only one primary address.
June 22, 2010 at 8:17 am
Eugene Elutin (6/22/2010)
You could add PrimaryAddressId column into your Person's table, that would ensure the person has only one primary address.
I think Eugene got the answer. Flag column in address table is not good solution for your question.
No Signature
June 22, 2010 at 9:49 am
Yes, I think that makes a lot of sense. I was looking at the problem from the wrong side. Thank you, Eugene.
June 23, 2010 at 10:57 am
Joe Celko (6/23/2010)
...1.Rows are not records.
2.SQL programmers do not use BIT flags; that was assembly language and punch cards.
3.Being a primary address of a person is a relationship and not an attribute.
...
1. Rows are not records, and more than that: they are not columns 😀
2. BIT datatype was left in SQL only to make it back-compatible with punch cards. So, only people like me, who used to code in assembly and read punch cards can use it - SQL programmers should only use modern dataypes (I guess to store Y/N flag XML would be the best)
3. primary address of a person is a relationship, therefore it should be set to the value from the folowing list: Single, Married, Divorced, Widowed 😀
Ok, seriously:
1. Rows or records, in relation to the database table, is the same thing, just a matter of name preference. Tell me what is difference?
2. BIT datatype is very usefull in some cases as any other out of available datatypes, so you use it when there is a case for it to be used!
3. Conceptually, the IsPrimary address is, of cause, the attribute of the address, but from design prospective it better to be placed onto the Person record in your case as your business rule says that only one address can be primary! It is not an universal rule, even if it sounds like it!
Even so it will be implemented as "relationship", you will, most likely always use it as an attribute of address when retrieveing addresses for a given person, something like:
SELECT a.AddressLine1, a.AddressLine2,
CASE WHEN p.PrimaryAddressID = a.AddressId THEN 'Y' ELSE 'N' END as IsPrimary
FROM Addresses a
JOIN Persons p on p.PersonId = a.AddressId
WHERE p.PersonId = @PersonId
Just one more small thing:
Never use smallint, I have used it once, and had overflow error when tried to put 40000 in it. Bad datatype, from the era of Intel 8080 CPU (fortran or algol programmers may be?). Always use NUMERIC(38) it can store big numbers! :w00t::-D:w00t:
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply