design problem

[Daniel Bello Urizarri]

Hello: A design subject:
I have two tables that define a similar data set and another different data
set.
Table: CableCopper (id, code, type...., Caliber, Pairs)
Table: CableFiber (id, code, type...., Mark, Caract)
And one third table for which it is not important if the cable is Fiber or
Copper, but needs to guarantee referential integrity with some cable.
Table: Joint (idCable...)
Some suggestion for a robust design?

 

[Tim Ferguson]

Table: CableCopper (id, code, type...., Caliber, Pairs)
Table: CableFiber (id, code, type...., Mark, Caract)
And one third table for which it is not important if the cable is
Fiber or Copper, but needs to guarantee referential integrity with
some cable. Table: Joint (idCable...)
Some suggestion for a robust design?

Sounds like a job for subtyping:

Cables(*IDNumber (autonumber),
Code, CableType)

CopperWires(*IDNumber (Long int), Calibre,
Pairs) // IDNumber FK referencing Cables

Fibres(*IDNumber (Long Int), Mark, Caract)
// IDNumber FK referencing Cables


There is no way in Access of guaranteeing that a particular Cable record
cannot have CopperWire and Fibres record. You could move up to SQL server,
where you can use triggers, or else use a bit of code on the form and some
clean-up queries to check for anomalies in order to make it a bit unlikely.

Hope that helps


Tim F