"Shogun" <ar***********@ gmail.comwrote in message
<11************ *********@d30g2 000prg.googlegr oups.com>:
I have an mdb with a *projects* table. I also have a *timecode*
table.
Projects and timecodes are related by a one-to-one relationship but I
do not want to simply add the timecode field to my projects table for
the following reasons:
-Not all projects have a timecode
-Not all timecodes have a project initially.
The timecodes table can be viewed as a pool of codes. We add a
series of available codes to the table and assign them to projects as
time goes by. When we're out, we add more.
I thus would like to be able to assign a timecode to a project from
the projects table. This is easily done. The tricky part is:
I would like there to be some of way to feedback this assignment to
the timecode table in such a way that it is possible to open up the
timecode table and see which codes have been assigned and which ones
are still available.
I'm looking for a simple way of implementing this. I'm not using
forms or anything fancy. Users of my db will simply edit tables
manually. I'm basically building something between an excel
spreadsheet and a full scale db with ui.
Many thanks for considering this.
Many thanks.
If you wish to pursue this from a RI/1:1 relationship point of view,
then I think there are at least two types of 1:1 relationships,
asymmetric and symmetric. To play with it, consider table A and table
B.
Asymmetric – here there will be a “main table” (A) and a referencing
table (B). The main table will contain all records, say employees, and
the referencing table, might contain far fewer records (say information
about some few employees who need some special certifications to do
their jobs). The usual construct, would be to have the primary key from
the referenced table be the foreign key (and primary key) of the
referencing table.
One A may relate to zero or one B
One B relates to one and only one A
The relationship is created with RI where the fields on both sides are
indexed without dupes.
This is the most common, if such term can be used on 1:1 relationships,
of 1:1 relationships.
Symmetric – which this seems to be, both tables are “equal” in the
relationship.
One A may relate to zero or one B
One B may relate to zero or one A.
Same as with M:N relationship, this can’t be implemented directly in
the
database, but will need a junction table. The difference between
resolving a symmetric 1:1 relationship vs resolving a M:N relationship,
is that when resolving the symmetric 1:1 relationship, one does so by
creating two asymmetric 1:1 relationships.
I e, in the junction table AB, the primary key of both table A and
table
B is present, both constrained UNIQUE NOT NULL (Indexed, no dupes,
required, no ZLS). In effect this will allow for projects without
timecode and timecodes without projects, and only the projects having a
timecode and timecodes assigned to project will be found in this
junction table. An outer join between the junction table and the
timecode table should give you the available timecodes, and RI will
prevent assigning of timecodes to projects where either the timecode is
alredy used or it doesn’t exist in the timecode table.
One might argue that this approach is a wee bit academic/theoretic, but
I think it should resolve the issue you’re working with.
--
Roy-Vidar
