Relationship 1:1

[MrLestat]

Hi,
First of all, i'm sorry for my poor english.

I have following Problem:
"I want to store incoming and outcoming documents. Documents are of particular type (i.e. HR docs, Personal files, Medical Documentation itp.). Each type of document has different unique data that i want to store and there is a few basic data which is the same for every type of document."

I have created tables for this:

t_doctypes { PK:id_doctype, description }

t_inbox {PK:id_document, id_doctype, id_sender, createdate, is_valid}

t_doc_l4 {PK:id_docdetails, id_document, field1, field2 ... other fields}

t_doc_l5 {PK:id_docdetails, id_document, field3, field4 ... other fields}

Now what i want is :

1. Relationships (1:1) between t_inbox and t_doc_l4, t_doc_l5
-> because one document in t_inbox has exactly one details record in only one of the tables (t_doc_l4 or t_doc_l5)
-> because i want to automatically delete linked rows

2. triggers on tables t_doc_l4, t_doc_l5
-> when the user adds a new document of type "L4" i want to automatically add a new record to t_inbox and update field t_doc_l4.id_document with value id_document of new record in t_inbox

Example of valid data in the tables:

t_inbox:
(id_document, id_doctype, ...)
1, "L4", 2011-05-10,...
2, "L5", 2011-05-12,...
3, "L5", 2011-05-13,...


4, "L4", 2011-05-16,...

t_doc_l4:
(id_docdetails, id_document, ...)
1, 1, ...
2, 4, ...

t_doc_l5:
(id_docdetails, id_document, ...)
1, 2, ...
2, 3, ...

Please help me with this.
1. I don't know if i designed tables right
2. I don't know how to create correct relationships
3. I tried to create trigger "After insert" but in that section access do not allow to update values of newly created record. how to do that?


Thank You for any help

[orange]

Is this a real "for business project" or a "learning exercise/term project"? Is this for your personal documents or a company/divisions/people?

There are many aspects to document classification and retrieval, especially in a multiuse/multiuser setting; and if you are trying to reduce the effort for meaningful, focused retrieval.

Look at a few of these links and identify the scope of your project. I'm not trying to be negative, but just showing the range of things that could be involved.

http://www.cs.utexas.edu/users/hyukc...Algorithm.html
http://en.wikipedia.org/wiki/Dewey_D...Classification
http://www.census.gov/eos/www/naics/

If it's for yourself or a limited number of people, you might want to create a data model; and take 50/60 sample records and see how well your approach works. Does it give you the info and retrieval methods you want? Adjust as required, until you're happy with the approach.
If it will be a "Standard Operating Procedure" for your organization, do not underestimate the importance of training, consistency of use and communications etc.

These systems work well when they are used in a standard manner.

I think you will improve chances for success by doing some modeling and refinement, before you create Forms and tables (database construction).

Lot's of questions, but intended to help you focus on what you really need.

Good luck.