Topic awaiting preservation: Database Design Question (Page 1 of 1)

I have two entities,

People and Places

Places has people (think of this as people working at the place)

Both people and places have notes (a simple blob field or text field)

I want to have only 1 table for notes and have both people and places reference this table for theirc notes. How should this be done?

The issue is referencing the unique ID's because Company will have an ID 1 as will people.

It is trivial to make two different tables Places_Notes and People_Notes, but I think it would be slick if there is a way to keep all notes in a single table.

If you have any ideas I would love to hear them.

Dan @ Code Town

come in again, what kind of x-to-y relation ships do you have.
One note/person and one note/project.
Or multiple?
Then you'll probably need two p*_note linking tables...

If I understand you correctly, this is how I'd do it.

People Table
---------------
Person Id
Name
Other Stuff
Place ID (you said places can have people. If that is a many to 1 relationship, this is where to put that.)

Places Table
--------------
Place Id
Name
Other Stuff

Notes Table
-------------
Notes Id
Note Type (People or Place)
Connecting ID (Either Place Id or Person Id to connect w/ one of the above 2 tables)
Other Stuff
Note


If you have a many people to many place relationship you'll also need the following:
(and you'll be able to remove the Places Id field from the People Table)

Relationships Table
-----------------------
Relationship Id
Person Id
Place Id

It seems simple enough to do it like this -

notes_tbl

notes_id
place_id
person_id
notes
whatever

when checking for notes for a place, look for the place_id field.

when checking for notes for a person, check in the person_id field.

The poster has demanded we remove all his contributions, less he takes legal action.
We have done so.
Now Tyberius Prime expects him to start complaining that we removed his 'free speech' since this message will replace all of his posts, past and future.
Don't follow his example - seek real life help first.

why not ?

note_tbl
id
note
...

people_tbl
id
note_id
...

place_tbl
id
note_id
...


And of course a relationship table betwen people_tbl and place_tbl. Oh, and like InI said this thread better belongs to Server-Side Scripting - Oh my!

The poster has demanded we remove all his contributions, less he takes legal action.
We have done so.
Now Tyberius Prime expects him to start complaining that we removed his 'free speech' since this message will replace all of his posts, past and future.
Don't follow his example - seek real life help first.

I'm certainly not a databse expert, but all the other suggestions just seem to add too much complexity with no benefit that I can see.

The only reason I can see for an associative table is if a single note entry will have multiple people (or multiple places) for it, which seems kind of odd.

{{edit - I misread po1's last post.

It seems likely that a person or place could have multiple notes, in which case your solution would be prohibitive.

If a person or place was only going to have one possible note entry, then it would make more sense to simply have a 'note' field right in the person table and place table, IMO.




(Edited by DL-44 on 01-20-2005 03:35)

my bad, I assumed the people & places could have only 1 note.

The poster has demanded we remove all his contributions, less he takes legal action.
We have done so.
Now Tyberius Prime expects him to start complaining that we removed his 'free speech' since this message will replace all of his posts, past and future.
Don't follow his example - seek real life help first.

I like Mobrul's idea the best. It keeps it simple by capturing a single id, and by differentiating the note by "Note Type".

The poster has demanded we remove all his contributions, less he takes legal action.
We have done so.
Now Tyberius Prime expects him to start complaining that we removed his 'free speech' since this message will replace all of his posts, past and future.
Don't follow his example - seek real life help first.

Tell me more about this Merise modeling method.

I, also, am no db expert. I simply use what one might call "common sense" to figure out what goes where. It "works", though, I'll admit, it often isn't the best option.

InI,
Would you mind taking a minute to explain in greater depth why DL's method is better than the others? I'd like the opportunity to learn a little something today.

Thanks.

The poster has demanded we remove all his contributions, less he takes legal action.
We have done so.
Now Tyberius Prime expects him to start complaining that we removed his 'free speech' since this message will replace all of his posts, past and future.
Don't follow his example - seek real life help first.

I was looking for this thread! I thought that I must have just not posted it at all. Thanks for the solutions. I will be look at implementing them.

Dan @ Code Town

Moved threads get noted at the bottom of the list of their previous homes.

-



.:[ Never resist a perfect moment ]:.

Thanks, InI.
I'm still not sure I totally understand, but at least I have a base of knowledge for further research.

What I think is missing here is the nuts and bolts of database structures. Advanced theories of mapping problems to databases means little without a strong RDBMS background, and WarMage's problem seems pretty straightforward. For anyone curious about databases, I recommend starting here:

http://www.devshed.com/c/a/MySQL/An-Introduction-to-Database-Normalization

and the subsequent article to learn about database normalization.

(Edited by jiblet on 01-21-2005 16:28)

The poster has demanded we remove all his contributions, less he takes legal action.
We have done so.
Now Tyberius Prime expects him to start complaining that we removed his 'free speech' since this message will replace all of his posts, past and future.
Don't follow his example - seek real life help first.