0

Let us say I'm making a phone book.

class Friends

  • ->FirstName
  • ->LastName
  • ->City
  • ->State
  • ->ZIP
  • ->Phone

From what I understand it would be best practice for me to have two tables in my database though for something like this with FirstName, LastName, Phone in one and the Address info in a separate table then use a ForeignKey to connect them. This way if two friends live in the same address I'm not repeating any information.

Should I be setting the class up with an INNER JOIN query then?

I haven't settled on a framework at this point so if you could also tell me:

In CakePHP would I be able to use the INNER JOIN to create a class or would that go against convention? If not would I be better served using a different framework like Laravel, Zend, Yii, Symfony or CodeIgniter instead?

A person could have multiple locations say a company for example that has two different offices, or a friend that has a summer and winter home.

Ryan
  • 699
  • 4
  • 13
  • 30
  • I recommend reading [normalization](http://en.wikipedia.org/wiki/Database_normalization). Then reading [when to denormalize](http://stackoverflow.com/questions/4301089/when-to-denormalize-a-database-design). Then you'll still have the same question :) – Jason McCreary Feb 19 '13 at 19:02
  • Thanks I've read some on both and will certainly continue to read more, especially if anyone has a book recommendation. But I'd still like to know more about how to plan my tables for use in a class and framework. – Ryan Feb 19 '13 at 19:05

3 Answers3

1

This to me is a questionable case for normalization. Yes, theoretically you could save yourself some redundant entries by having address normalized, but I would really think about how you are going to treat addresses in the system before making this decision. Are addresses always going to be a property of a user (one to one relationship), or are you intending to actually have some sort of address management where a user can have multiple addresses? In the former case, I probably would not normalize, whereas in the latter case, I definitely would.

It really boils to to whether the address, as a stand along object, has any meaning in your system or whether the address is just a one-to-one property of the user.

Mike Brant
  • 70,514
  • 10
  • 99
  • 103
  • Yes a person could have multiple locations say a company for example that has two different offices. I'll edit my question to clarify this. – Ryan Feb 19 '13 at 19:12
  • Are you using "normalization" as a synonym for "table decomposition"? In the one-to-one case, I don't see where decomposing person data and address data accomplishes any normalization at all. – Walter Mitty Feb 19 '13 at 20:36
  • @WalterMitty "Normalization" is the term typically used for organizing tables in a database such that data is not duplicated in a redundant fashion. This normalization process typically includes looking at how to split data in a large table into multiple smaller tables which a relationship expressed between them in terms of a foreign key. – Mike Brant Feb 19 '13 at 21:07
  • @Mike. Good. You use normalize in a formal way, as I do. Note that in the second case you state, you are bringing the data into conformance with 2NF. In the first case, decomposing would not be "normalizing" as far as I can see. – Walter Mitty Feb 20 '13 at 19:23
  • @WalterMitty Yes, I wouldn't suggest just decomposing data at all. In the first cases I was basically stating to leave table as is with all columns in a single table. Only if you needed to manage multiple addresses per user would I normalize the address data into a second table (with one-to-many realtionship). And only if I had the need to be able to relate multiple users to a single address (i.e. look-up multiple users per address) would I establish a many-to-many relationship between users and addresses across a join table. – Mike Brant Feb 20 '13 at 20:55
  • @Mike. You and I are basically on the same page. I would have been less confused if you had said, "this is a questionable case for table decomposition". In any event, I concure with the advice you have given the OP. – Walter Mitty Feb 20 '13 at 21:52
0

You would only need to store addresses in a seperate table if you are planning on having a person be associated with more than one address at a time. If each person will have only one address just put it in the main table. Phone books as I remember them only associate a person with one address...

That being said, if you do go multiple tables you can most likely stick with one class, and perform the join.

Jared Meyering
  • 1,291
  • 2
  • 11
  • 21
  • I've edited the question based on responses - yes a person could be associated with multiple addresses – Ryan Feb 19 '13 at 19:14
0

This way if two friends live in the same address I'm not repeating any information.

For "precise" data that might be fine, but addresses tend to be "fuzzy". People will express them in different ways and sometimes misspell them - if that happens, which address should you use: the one that is already in the database or the new one? How would you even know they are actually the same, and which one is misspelled? It's probably better to "isolate" the addresses so any errors or variations stay "local" to the person they originated from.

On a more philosophical note: if you go that way, why not "normalize" streets, ZIPs, cities, countries or even (land-line) telephone numbers? You could do that with "precise" data, but for addresses, attempting to push this concept to the extreme will just make the whole system brittle.

That being said, it is perfectly reasonable to allow a single person to have multiple addresses, by putting the address into separate table connected via FK (address references person, not the other way around), otherwise just keep the address in the same table.

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167