Page 1 of 1

Class and table design

Posted: Wed Jan 04, 2006 6:48 pm
by tyreth
Hi,

I've noticed more and more that as I'm designing classes and tables, that the tables in a database resemble a class very strongly. This causes me to question certain design choices.

Take, for example, a table of "documents". This table may have fields
* user_id
* date_added
* file_title
* file_type (ENUM)
* file_data (blob)
* parent_owner_type
* parent_owner_id

The last field is where I have the difficulty. Imagine I have a 'conversations' table attached to a 'contacts' table. This 'conversations' table details instances of followups with that contact. Eg, I might write:
"Customer complained about noise next door. See attached email". In this case, I attach an email file that is stored in the documents table, and associated with the 'conversation_id' of the conversations table. So then I set 'parent_owner_type="CONVERSATION" and parent_owner_id=24' so that I know which table type it belongs to.

Then I have an 'alarms' table which has alarms in it which set off at certain times to warn the user. An 'alarm' may have documents attached. So I put 'parent_owner_type="ALARM" and parent_owner_id=53' so I know to search by that.

Then there are other tables with their own unique id fields as well. So I find that for my documents system I need to code a separate file. The other thing is that with such a design I can't have 'parent_owner_id' as a foreign key. So then I need to code in PHP the logic necessary to filter out and select 'documents' appropriate to the table being used.

Is there a better way to design tables that removes the necessity of me having to specify both 'table' and 'id' that a row belongs to?

I hope I'm making sense here.

Thanks,
Mark