Req: Access db relationship issue - should this table be 1-2-1 ?

[RealClass]

Hi,

Thanks for taking time out to read this. This is essentially a db design quandary.

I've been setting up an asset management db for a almost a year now and trying to tie all the assets on the network together.

Basically I have stations which comprise assets (base unit, monitor, keyboard, mouse etc.) - a station can be a desk or a network cabinet or an area of the warehouse - it's very open.

I have a station table and asset table (amongst others) and some of the assets (base units, phones, printers etc) can connect to the network through RJ-45 ports - the ones on the wall.

This is where my problem initially started - the RJ-45 sockets.

I originally had each asset holding a field for the RJ-45 info, but then if I moved the asset to another station, it had to loose its' RJ-45 info - since it was no longer connected to that wall
socket. That worked ok because I just added the new RJ-45 info. later to whichever base unit took its' place.

Then I decided to go for a one-to-one relationship with the assets to fixtures (RJ-45's) and build a Fixtures table. So when an asset is moved the Fixture link is deleted - and new ones are added when
needed.

This works ok - so far. Essentially I only hold Fixtures provided that they are attached to a network asset. Once a fixture is no longer connected to a network asset it is deleted from the fixtures
table.

This is my problem - I think I should keep details of all fixtures so I can attach a fixtureFault table - so I can keep info. on which RJ-45's are faulty (and they do go faulty as you well know) so I
can bring them into the Fault report.

But if I remove them when an asset is moved that won't work. But if I keep them on the one-to-one relationship then that seems to violate good db design - also I would not have the 'dead' fixtures
connected to an asset - so I would have null values in the asset id's for those fixtures. Essentially 'hanging' ports not connected to anything.

So if you got this far and understood what I said then thanks...

Should I break relationships with this table altogether and maintain it with code ?

Or should I have done this design differently from the beginning ?

I hope someone understands this enough to help.

I'm ok with the coding but this one bit of the system has been a pain ever since I started to build this db.

Many thanks in advance - if only for your tolerance at reading it.

Regards,
Dave

 

[tkelley via AccessMonster.com]

How about making the Fixture table it's own parent table, instead of a child
table of the Assets table. Basically reverse the direction of your
relationship from Fixtures ==> Assets, instead of Assets ==> Fixtures.

Fixtures is a master table, then if that fixture is being used in an Asset,
then there is a FixtureID field in the Asset table. You retain referential
integerity, etc. But that way each fixture is not required to be in an asset,
but to assign an asset a fixture, it still must exist in the master Fixtures
table. No nulls necessary.

Does that make sense?

And bless you for caring about referential integrity and sound design. I
work at a place where my predecessors were abysmal about it.

 

[Clifford Bass]

Hi Dave,

I would add to Kelly's comments that doing it his way you can then just
add a defective/working status column to the fixtures table and record the
status of each. An added possibility may be to add in start and end dates so
you can record the adding and removal of the fixtures.

Clifford Bass

 

[RealClass]

Hi tkelley,

Many thanks for the response, I must say I didn't expect anyone to even try to understand the problem !

Thank you for your views - and I must say I think you are spot on !

I think my view in the past was always clouded into thinking that these fixtures were somehow an asset in themselves, and maintained a belief that they should always have been a 'part' of the asset
structure.

The changes you outlined are not only relatively easy to implement, but make good design sense. I do now think that these fixtures are masters in their own right.

Hopefully I can put this to bed soon - it will no longer always be nagging in the back of my mind that I'm not 100% convinced about the design stability.

Many thanks again,
Dave

 

[RealClass]

Hi Clifford,

Thanks for the reply, these are good suggestions - I will take them on board.

Kind regards,
Dave

 

[Evi]

Here is another possibility, if I understand rightly that a fixture is an
asset that can be connected to a port. If so, what about adding a tick field
to your Assets table to mark those assets that have the potential to be
fixtures, then have an RJ-45 table listing your ports and where they are so
that they continue to exist even if nothing is plugged into them.
Then have an AssetAndRJ-45 table where a fixture can be joined with port.
Your Asset combo will filter out only those Assets which have the potential
to become fixtures.
(The primary keys of the Asset and Ports tables are foreign keys in this
table)
This table would contain an error field for if the Asset is faulty when
plugged into the port, a Time and date field would record when the Asset was
plugged into the port and a Time and date field to record when it was
removed from the port.
This means that you will be able to keep a historical record of which items
were joined to the port and when.
Find Unmatched queries will list which ports and which 'fixture-able' assets
are not being used.
Evi

Hi Clifford,

Thanks for the reply, these are good suggestions - I will take them on board.

Kind regards,
Dave

 

[Fred]

We went through this twice, both with I.T. assets and telephones. One
additionl note/ shortcut.

You have to start with your "mission statement" including:

A. Which are the items where you explicitly want to track them. It appears
that this is assets. (and, of course, you need to decide on your definition
of "assets")

B. Which are the items that you have (full or partial) lists on (only)
because they provide information about "A" items. It appears that for your
this is jacks, fixtures locations. and data connections and placements of
assets.

That will help guide your work.