One-to-one relationships as feedback

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.

re: One-to-one relationships as feedback

On Thu, 21 Jun 2007 18:20:40 -0000, Shogun <arisaivaladez@gmail.com>
wrote:

re: One-to-one relationships as feedback

On Thu, 21 Jun 2007 18:20:40 -0000, Shogun <arisaivaladez@gmail.com>
wrote:

I'm not sure those are good reasons to not keep the timecode in the
projects table. Say you have a Customers table. You don't know the
FirstName of every Customer. Taking your logic I could create a
FirstNames table and only store those FirstNames there that I know. If
you read up on Normal Forms this actually has a name, and it's not
often used in the business productivity apps that Access is targeted
for.
The much simpler solution is to have FirstName as a not-required
field, or TimeCode as a not-required field.

You say "When we are out, we add more". Perhaps they could be
generated programmatically? If yes, one could be generated at the very
moment it is needed, so you would no longer need a timecodes table.

If it can be generated, it's even debatable whether it should be
stored: there are database design rules against storing calculated
data, but that's out of scope of this posting.

I understand you currently have a timecodes table with a single field:
TimeCode. If that is so, you don't have a 1:1 relationship (but rather
two standalone unrelated tables); that would require a second field:
ProjectID (not required). If you have ProjectID in your table, the
question of "which codes have been assigned" is easily answered: the
records with a ProjectID.

You should really rethink this idea of allowing users in your tables.
It is very much frowned upon (I'm sure you can understand why, or
google for it)

-Tom.

re: One-to-one relationships as feedback

We get the new codes from another database and do not have control
over what they will be.

This mdb is for a team of 4 engineers familiar with SQL so I'm not
worried about leaving the tables open. We're currently using Excel
and the complexity is such that although the solution is usable, it's
not very practical.

I understand that what I'm doing is trying to solve a data-
presentation problem by modifying the data-model and how "bad" this is
but it's probably the quickest way for me to solve this. If this was
more important or if I had more time, I'd build a real db app. I'm
just trying to cut corners here.

Maybe I should just learn how to use Excel as a front-end for Access.

Thanks for your input. Let me know what you'd do.

re: One-to-one relationships as feedback

"Shogun" <arisaivaladez@gmail.comwrote in message
<1182450040.347326.48920@d30g2000prg.googlegroups. com>: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

re: One-to-one relationships as feedback

I have a 2 tables one is a list of available Cost codes "tblCostCode" the
other is a Job / Cost Code table "tblJobCost" that list cost codes assigned
to the job. When a user wants to add a new cost code to the job I use the
following query to show only unassigned codes.

SELECT DISTINCTROW tblCostCode.* FROM tblCostCode WHERE (((tblCostCode.Code)
Not In (SELECT tblJobCost.Code FROM tblJobCost WHERE
(((tblJobCost.Job="JobNumber"));))) ORDER BY tblCostCode.Code;

Something similar should work for you application.


"Shogun" <arisaivaladez@gmail.comwrote in message
news:1182450040.347326.48920@d30g2000prg.googlegro ups.com...