Help with MySQL database design

[justinmc]

I'm working on a database that will list a series of entities (namely, businesses or individuals), each entity will be associated with multiple events over a period of time (ie, corporate mergers, appointments, court cases, etc.). Each event can have supporting "assets" attached to it (ie, sec documents, press releases, images, etc.).

I'm fairly comfortable with my database design for all the above, it's not too complex. But I want to ask one more thing of my database -- what are the known associations between the entities? For example, entity A could be associated with entities C, D, F and G... and so on... Maybe this is a better example. I have entity "John Doe" whom I know once was in a partnership with entity "Jane Doe" and worked for entity "XYZ Corp" -- and I want to reflect that in the database.


I'm having trouble visualizing how to structure my database to accomplish that. Here's what I have so far (forgive me, I'm not sure the best way to communicate this, so I'll try my best).

entity (table)
--------------
entityid (pk)
entity_name
entity_type

event (table)
-------------
eventid (pk)
entityid (fk)
event_description
event_created
event_date
event_type

asset (table)
--------------
assetid (pk)
eventid (fk)
asset_name
asset_description
asset_file_name

association (table)
----------------
associationid (pk)


Obviously, I'm at a loss as to how to construct the association table and how it relates to the other tables. I'm sure it's fairly obvious, but I need some help. Any advice?

thanks,
Justin