Working with relational database (8)

1 Name: #!/usr/bin/anonymous : 2007-02-24 17:16 ID:cc3oFGo/

Is there a better way to connect person to a place in a relational database, rather than this? This way I end up with many tables, and it's a heck of a big (and boring) job to work with.


CREATE TABLE person (
int id PRIMARY_KEY AUTO_INCREMENT
);

CREATE TABLE place (
int id PRIMARY_KEY AUTO_INCREMENT,
varchar(100) city,
varchar(100) place
);

CREATE TABLE personPlace (
int person,
int place
) PRIMARY_KEY(person, place);


I will actually be using Postgresql on this project, not Mysql. But the question is the same.

2 Name: #!/usr/bin/anonymous : 2007-02-25 05:31 ID:pqVT8rm4

Step back to basics. A Place can have zero or more Persons in it, and a Person can be in exactly one place. Or a Person can be in zero or one places.

First approach (Place has zero or more Persons, Person is in exactly one place at all times):

CREATE TABLE Place (
  id INT PRIMARY KEY AUTO_INCREMENT,
  country VARCHAR(50),
  city VARCHAR(50)
);

CREATE TABLE Person (
  id INT PRIMARY KEY AUTO_INCREMENT,
  placeId INT NOT NULL,
  CONSTRAINT PersonPlaceIdFK
    FOREIGN KEY (placeId)
    REFERENCES Place(id)
);

Second approach (Place has zero or more Persons, Person can be in zero or one Place): Same as above, just remove the NOT NULL constraint from Person.placeId.

Also, I don't like the Place.place key you have going there: it's very vague.

3 Name: #!/usr/bin/anonymous : 2007-02-25 09:46 ID:cc3oFGo/

Thanks for your reply.

In my case I actually need:
Person has zero or more places, place has zero or more persons.

It's redundant, I know. But that's how the metadata is provided. The Person table is actually Picture table this project.

> Also, I don't like the Place.place key you have going there: it's very vague.

(city, place) combined does make a foreign key, but it seemed redundant data storage to me to use both attributes in personPlace table as a foreign key. I'm unsure what's right to do, comments appreciated.

4 Name: #!/usr/bin/anonymous : 2007-02-25 09:47 ID:Heaven

> I don't like the Place.place key

Sorry, I read that as Place.id for some reason in my previous reply

5 Name: #!/usr/bin/anonymous : 2007-02-26 02:42 ID:pqVT8rm4

> In my case I actually need:
> Person has zero or more places, place has zero or more persons.

In that case, you got it right the first time (many to many). You use a separate table to store foreign keys to the two other tables involved.

6 Post deleted.

7 Name: #!/usr/bin/anonymous : 2008-02-12 13:03 ID:YpJbE3Fb

>>6
wat

8 Name: #!/usr/bin/anonymous : 2008-02-14 12:29 ID:y9gVnAtd

Another option is to think up a name for the relationship and have that as a real model class instead of the join table approach. The end result on the database is similar but the code would be completely different and it would pave the way for the possibility that the relationship itself might one day have other attributes.

Return Entire thread Last 50 posts
This thread has been closed. You cannot post in this thread any longer.