"same as"

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

"same as"

Post by josh »

When you put a "same as yaddayadda" checkbox, do you normally have a "same_as" column, or do you just copy the data?

A little more explanation. Lets say you had a mailing and street address on a signup form. Would you just copy the fields via javascript and store the duplicated addresses? Or would you NULL out one of the addresses and have some field somewhere that signifies to look at the other (street) address?

The 'same_as' is kind of a smell because now I can't export that data and read it as easily, the database schema becomes more complex.

On the other hand if you just duplicate the data, the signup process is easier because you just duplicate the data, but when they update their profile you have to some how make sure edits to the mailing address also take affect to the street address (if they are supposed to be the same)

So something like

Code: Select all

 
if( $mailing->equals( $street ) ) // would internally compare all the strings or something.
{
 ... then update both identically ..
}
else
{
 .. then update them independantly
}
 

vs having a

'same_as' column.

The first option you create a code smell, the latter is a database smell. How do you deal with this pattern? I have it happening for all sorts of concepts other then addresses. Should I treat this concept uniformly throughout the system?

Or what strategies do you use to isolate this kind of implementation detail, so it can be changed in one place later. I guess the former psuedo code may not be a smell, and may be a good way to isolate the implementation detail, but I can't quite wrap my head around how I would do it in all layers ( from javascript in the UI down to the data schema in the storage layer). I mean I've done it 100x thats no problem, but how do I avoid creating a bunch of boilerplate code?

For example I have a form that asks for each person: phone, email, address, first & last name, job title, etc...

Then we ask this information for several people:

The main contact person
The person filling out the form
The person who will be using the services,
etc...

For each of these steps they should be able to easily tell us it is the same person, and we should be able to handle the data in a professional and robust way. In the legacy system all the data was just duplicated, and updating the 'mailing address' caused the addresses to no longer be synched up ( the user would have to know he was supposed to update both addresses when it changed)
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Re: "same as"

Post by superdezign »

Why even maintain a same_as column? I would simply make the duplicate column NULL.

Make it so that the user has a "same as" checkbox in their form. If it was checked when submitted, make sure only one field is filled, otherwise return an error. If it was not checked when submitted, make sure both fields are filled, otherwise return an error.

If it was checked and only one column was filled, then update the database with the new column value and the other value as NULL. If it was not checked and both columns were filled, update the database with both new column values. At no point does it matter that the current value in the database is NULL unless you are actually looking at that value. In which case, you could do a (MySQL) query like this:

Code: Select all

SELECT `address` AS (IFNULL(`street_address`, `mailing_address`)) FROM `customers` WHERE `id` = $id;
No idea if that actually works though. :P
Not in a position to test it.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: "same as"

Post by alex.barylski »

A little more explanation. Lets say you had a mailing and street address on a signup form. Would you just copy the fields via javascript and store the duplicated addresses? Or would you NULL out one of the addresses and have some field somewhere that signifies to look at the other (street) address?
Ran into a similar problem with shipping/billing address details...personally I chose to store the data separately and just copy the details over in the request object on the serverside and use JS to copy the details over if it's enabled.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: "same as"

Post by josh »

@superdesigns, thanks sounds like the best solution, as long as blank/NULL are not valid values (can't think of why they would need to be, but you never know)

@pcspectra, so if the user returns to the form, disables javascript, and edits the 1 address, for ex. fixes a typo, that typo would still be present on the other address? Or you have conditional validation like what superdesigns was mentioning?

Hmm what about more than 2 similar sub-forms, like the example with 3 people, where any 1 of the person forms could be "same as" any of the others, then I guess you need same_as columns, or a single same_as bitmask or something?

For ex, person 1 (Josh) could be same as person 3(Josh), while person 2 is different (not Josh)
or... persons 1,2 & 3 could all be josh
or... persons 1,2 & 3 could all be different people,
etc...

For example when we register our businesses in FL on http://www.sunbiz.org they have 10 sub-forms to tell about who plays different roles within the company, the form the user is filling out could be asking,

who is the CEO? (checkbox same as marketing directory, checkbox same as art director)
Who is the marketing directory? (checkbox same as CEO, checkbox same as art director)
Who is the art director (checkbox same as CEO, checkbox same as marketing director )

The more questions you add, the more checkboxes that are needed, and the more complex the data storage would become.

Perhaps separating (read decoupling) the concerns, would be called for, like a 'role' as well as 'person' table with an association or bridge table between the two, showing who plays which role(s)?

So

people
person_id | person_name
1 | Josh
2 | Mary Jane

roles
role_id | role_name
1 | CEO
2 | Complainer


person_roles
person_id | role_id
1 | 1 // Josh is A CEO
1 | 2 // josh is A complainer
2 | 2 // mary jane is A complainer

Or enforce 1:1 relationship between roles:people

person_roles
person_id | role_id
1 | 1 // Josh is THE CEO
2 | 2 // mary jane is THE complainer

This is actually the design I've been moving towards, in the legacy system we had "first_name, last_name, phone, fax, etc..." copied onto every table, and copied if there were different people owning that record. In the new design I have a person_table that has foreign keys in lots of tables, the person table is the largest in the application ( every user = a person, plus every additional role for each user is another person, for each user there is 1 person who IS the user, and maybe more who are just "contacts" at that company)

FYI I didn't make the legacy system
Post Reply