07-12-08, 09:34 AM #1
Any DB pros here?
I need some advice: I'm building a database to keep track of my friends and contacts. I have a number of tables for all kinds of things but I'm trying to think of the best way to handle addresses.
Currently, I have an 'address' table that looks like this:
CREATE TABLE `people_addresses` ( `id` INTEGER NOT NULL AUTO_INCREMENT , `id_people` INTEGER NOT NULL , `address` MEDIUMTEXT NOT NULL , `founded` DATE , `revoked` DATE, PRIMARY KEY (`id`) );
The entire structure can be viewed here (click save/load... load... 'blue_uk' - obviously, don't save over it even though I've got backups!)
07-12-08, 08:50 PM #2
Personally I'd suggest doing it what ever way you feel comfortable with. You could go to the extents of looking at the ergonomics of saving space in the database or lessening the number of database accesses, however you've then got to identify if this inhibits your database at all. I mean your friends might have the same address now but how does your database handle them moving away from one another and having different addresses? do you keep the old address/es on record etc?
I guess it's up to you to decide if you want to over complex the system to deal with those "extreme" cases or do like most people do and just do the most straight forwards and simple method and retrofit the database in the future should such a situation arise. (This is where obviously Documenting such projects comes in real important, so you know what you're plan was for retrofitting since it could be months/years down the road.)
Personally the way I'd do it is have the addresses in their own table with a unique identifier per new address. This identifier can then be used as a key from other tables to point to the correct address, should their be an address change the old key could be logged and a new entry made with a incremented Identifier number. This way you get the new address and keep their old on record. (Obviously this doesn't cater for "editing" existing records.)
07-12-08, 10:24 PM #3
Perhaps I'll just do it as it is and make sure I type the addresses exactly the same!
07-14-08, 05:24 PM #4
If it was for me, I'd make it as you did, but having an unique ID for each person as the address table's only key (foreign key from the friends table). I'd also add a status flag column with values like 0-not currently living and 1-currently living. That way, you can add new adresses, friends can have more than one house or can list their parent's house, and when you select, you put status = 1 in the where clause to know at which one he is currently living.
Also, I think the auto increment ID you have as key there is unecessary. Since you have a 1 to n relationship between the friends table and the address table, that number wouldnt really mean anything to you. The adress alone is of no use to you without a person attached to it... so just use the person's ID for key.
07-15-08, 08:24 AM #5make sure I type the addresses exactly the same!
In your case the people address relationship would be a many-to-many relationship: a person can have multiple addresses and an address can be associated with multiple people. It would typically be modeled using an relationship table. e.g.
rename the existing people_address table to addresses and drop the id_people column.
You might also consider adding a text column to the addresses table so that you can annotate the type of address (home address, mailing address, ...)
07-17-08, 01:09 PM #6
I've effected massive changes to my database. I am currently try to whore as much data as I can. My goal is to obtain the UK PAF file, which contains all postcodes and assosiated addresses.
I've changed the address table to:Code:
CREATE TABLE `people_addresses` ( `id` INTEGER NOT NULL AUTO_INCREMENT , `id_people` INTEGER NOT NULL , `property` VARCHAR(30) , `outcode` CHAR(4) , `incode` CHAR(3) , `name` MEDIUMTEXT , `founded` DATE , `revoked` DATE , PRIMARY KEY (`id`) );
The outcodes reference another table which provides GPS coordinates etc.
07-17-08, 01:11 PM #7
By lixluke in forum Science & SocietyLast Post: 12-16-08, 05:14 PMReplies: 58
By supertech in forum Computer Science & CultureLast Post: 04-30-08, 12:57 AMReplies: 0
By Tiassa in forum SciFi & FantasyLast Post: 01-02-08, 01:57 AMReplies: 14
By geekzilla in forum SciFi & FantasyLast Post: 09-11-07, 08:58 AMReplies: 5
By Atom in forum Science & SocietyLast Post: 08-24-07, 06:54 AMReplies: 54