Database design

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:

Code:

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`)
);

However, I know lots of people who live at the same address. Would it be better to have yet another table that defines 'properties' and then link that in?

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!)

 

Log in or Sign up to hide all adverts.

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.)

 

Log in or Sign up to hide all adverts.

Perhaps I'll just do it as it is and make sure I type the addresses exactly the same!

 

Log in or Sign up to hide all adverts.

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.

 

Saying something like that usually indicates that you might want to revisit your design. The point in this case is to try to minimize both the amount of work you have to do and the potential for errors in your data.

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.

people_addresses
id_people
id_address

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, ...)

 

I don't understand. I can't use the person's id as the key field because some people have more than one address, so the key would not be unique?

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`)
);

Where I enforce EITHER property,outcode,incode corresponding toa UK address (.e.g '12B' 'SN7' 'P12') or 'name' which is a varchar field for foreign addresses.

The outcodes reference another table which provides GPS coordinates etc.

 

Good idea, and I'd probably do it referencing another [small] table of address_types.