Implications of entity one-to-one relationships

[NotionCommotion]

I can create a one-to-one relationship between two tables by placing a unique constraint on the foreign key and making it NOT NULL, and make it one-to-at-most-one by removing the NOT NULL constraint.

My question is what criteria should one use to determine which table holds the foreign key?

For instance, I have the Car and Motor entities as shown below where one Car has one Motor, and conversely one Motor is used by one Car.

AbstractPart
- id (Auto Inc)
- discriminator (car, motor, etc)
- serialNumber (unique for given discriminator)
- manufacturerId
- weight
- cost
- countryOfOrigin
- etc

Car extends AbstractPart
Motor extends AbstractPart

What reasoning should one use to determine whether the Car table contain the Motor ID or should the Motor table contain the Car ID?

[Zane]

Cars contain a motor.  Motors do not contain cars.

Therefore, put a column for motor in the Cars table and link through the various motor ids.

[NotionCommotion]

I truly appreciate your unequivocal advise.  Do you feel that cascade delete using the DB should sometimes/often not be used?

[Barand]

No provision in your system then for a motor needing to be replaced?

[requinix]

Notion, is this another one of your famous unnecessarily abstract questions? Using it to get generic answers to a specific problem?

[NotionCommotion]

9 hours ago, Barand said:

No provision in your system then for a motor needing to be replaced?

Negative.  A provision must be provided to replace a motor.

[Barand]

But no service record of those replacements - what , where, when ?

[NotionCommotion]

7 hours ago, requinix said:

Notion, is this another one of your famous unnecessarily abstract questions? Using it to get generic answers to a specific problem?

Yes infamous and abstract and maybe unnecessarily so.  I hesitated to make it so, but also was concerned about either adding too much minutiae and wasting other's time or not providing enough so one couldn't understand why I am doing such.

An AbstractDataSource provides time based physical data and uses one of several means to obtain the data.  A GatewayDataSource extends this class and utilizes a hardware gateway on a foreign network to obtain the data.

There are several types of Gateways (bacnet, dali, modbus, opc, etc) which extend AbstractGateway, and there is a one-to-one relationship between GatewayDataSource and a gateway.

Some of these gateways use a Device which both reflects other 3rd party controllers on the foreign network as well as the gateway itself, and I have classes 3rdPartyDevice and GatewayDevice which extend Device.  There is a one-to-one relationship between the gateway and the gatewayDevice.

A dataSource associated with a gateway which is associated with a device can be deleted, however, any associated 3rdPartyDevices must not be deleted but only tagged as deleted so that once its auto-increment PK is generated, it will always remain and reused should a device with specific natural key identifiers later be reinstated.

To repeat the question with more context, should a foreign key be placed in GatewayDataSource or AbstractGateway?  The same question applies to a gateway and a device.

[NotionCommotion]

19 minutes ago, Barand said:

But no service record of those replacements - what , where, when ?

Correct, no service record.  I suppose I could do so especially if it simplifies the schema and implementation, but currently don't have such a requirement.

[Barand]

Perhaps it's  just as well. That would take the relationship to 1-to-many or, potentially, a many-to-many.

[NotionCommotion]

Thanks Barand,  Do you agree with Zane's assessment?

On 11/4/2019 at 6:50 PM, Zane said:

Cars contain a motor.  Motors do not contain cars.

Therefore, put a column for motor in the Cars table and link through the various motor ids.

 

[Barand]

Motors drive cars; cars do not drive motors, ergo ...

You could sidestep the question if you can't make your mind up

+------------+          +------------+            +------------+           
|    car     |---------<| car_motor  |>-----------|   motor    |           
+------------+          +------------+            +------------+

Nothing says there have to be "many" in a many-to-many type structure

[NotionCommotion]

2 hours ago, Barand said:

 

+------------+          +------------+            +------------+           
|    car     |---------<| car_motor  |>-----------|   motor    |           
+------------+          +------------+            +------------+

Nothing says there have to be "many" in a many-to-many type structure

And a couple of unique constraints can be used to limit to one on both sides.  Then, getters and setters will need to filter to the first entry in each collection.  But why bother? 

[Barand]

 

48 minutes ago, NotionCommotion said:

But why bother? 

There's an old adage that says when faced with "the horns of dilemma" ...

On 11/5/2019 at 1:58 AM, NotionCommotion said:

 whether the Car table contain the Motor ID or should the Motor table contain the Car ID?

… look for a third alternative. I was trying to provide one for you.

[maxxd]

8 hours ago, NotionCommotion said:

And a couple of unique constraints can be used to limit to one on both sides.

I strongly recommend against that.

As one who will drive a car until it falls apart completely, I can see a situation where a single car can could have multiple motors, the defining data of which could be repeated. [Disclaimer: I know nothing about cars or engines, so please don't take my example as a real-world situation] If my Honda Element has a Hemi when I buy it, but I drive it to the point that the hemi dies and I put a V8 in there, cool with the unique limiter. However, if I then drive the same Element until the V8 dies and I decide to put different hemi in there, well then there's a problem.

Play it safe for the future - Barand's junction table is the way to go; even if you don't use it as a many-to-many junction point right now, you may have to later.

[NotionCommotion]

7 hours ago, maxxd said:

If my Honda Element has a Hemi when I buy it,

Maxxd, where can I get a Honda with a Hemi or a V8?  Been looking for one, but they don't seem to be available

I see your and Barand's point and do not disagree for the common attributes, but it does not address the specific attributes such as the engine serial number or the torque I cinched down the spark plugs which make it unique.  In hindsight my abstract example was flawed as all of the properties were common which should utilize a many-to-many except for the serial number, and apologize for the poor example.  For my real application, I have device identifier, timeout settings, buffer size, etc, for some types of devices and other similar attributes for different types of devices, which are all specified by the user and used to configure each device.  Maybe I should have included these attributes in some parent table/entity, however, injection brought some flexibility and simplification which leaves me with these one-to-ones.

[kicken]

I'm not too sure what your going for with your gateways, devices, etc.  If you haven't yet reached a conclusion to your problem, I'd suggest trying to refine the details on that some.

As I understand it you have some AbstractDataSource type which is responsible for gathering data from somewhere and GatewayDataSource is a specific type of source.

GatewayDataSource uses some sort of AbstractGateway to obtain that data (different specific gateway types exist).

So it would seem at this point you have essentially
 - AbstractDataSource/GatewayDataSource: Requires a gateway instance so a foreign key is needed.  You're unsure whether the foreign key should exist in the AbstractDataSource or AbstractGateway tables.
 - AbstractGateway: Might use a device so it should have a (NULLable?) foreign key to a specific device.  You're unsure whether the foreign key should exist in the AbstractGateway or Device tables.
 - Device: Standalone object

It sounds to me currently like your gateway and device object might be setup to be shared instances that different data sources just link to.  If that's the case, then keep the references in the data source.  Have your source link to both the gateway and device and composite them together as needed.

If every data source gets it's own unique gateway, then you could instead link the source to the gateway then link the gateway to the device.

This is the part of your post that is causing me issues trying to understand what kind of setup you have and thus how it should be structured.

On 11/5/2019 at 1:22 PM, NotionCommotion said:

A dataSource associated with a gateway which is associated with a device can be deleted, however, any associated 3rdPartyDevices must not be deleted but only tagged as deleted so that once its auto-increment PK is generated, it will always remain and reused should a device with specific natural key identifiers later be reinstated.

This implies to me your devices (and maybe gateways) are some kind of shared resource meaning many gateways may link to the same device so if a gateway is deleted it shouldn't be deleting the device as it might still be used by others (now or later).

It might be helpful for explaining the situation to focus more on explaining how the physical networks your trying interact with work and what you need to do with them, rather than or in addition to the code your trying to write.

 

 

On a side note to the evolved car/motor example (which doesn't seem like the same situation to me), in the past I've essentially made two relationships for such situations.

1) Car has some sort of CurrentMotorId foreign key that would reference whichever motor is currently being used by the car

2) Every motor has a CarId foreign key reference to indicate which car that motor was used in (either in the motor table or a linking table if motors are shared between different cars over time)

Current car operation can be handled using the first relationship.  Historical service data can be found using the second relationship. 

 

[Zane]

From what I understand so far, you're wanting to keep up with car parts, no?  I'm not an auto expert or anything, I'm just trying to get a feel for what exactly you're aiming for here.  Either you're trying to keep a record of engines, engine parts, and just auto parts in general, and you're wanting to be able to link certain parts to the cars/autos they're available/compatible with,... or, you're just keeping up with a list of cars and what motor(s) they have/had for e.g a car lot where car salesmen sell cars and keep up with the inventory.  Or, perhaps, you're working on a use case catered to mechanics who fix cars and want a history of all of the parts this car has every had in its lifetime.  Anyway, it'd help to explain what you're trying to accomplish.

[maxxd]

12 hours ago, NotionCommotion said:

Maxxd, where can I get a Honda with a Hemi or a V8?  Been looking for one, but they don't seem to be available

Yeah, I got nothin'. The only thing I know about cars is where the gas goes and where to take it when something sounds weird.

12 hours ago, NotionCommotion said:

it does not address the specific attributes such as the engine serial number

Again, I know very little but serial numbers should be specific to each engine, correct? So that doesn't seem like an issue in any way.

12 hours ago, NotionCommotion said:

or the torque I cinched down the spark plugs which make it unique

Wut?

At any rate, I would think if you're trying to keep track of modifications to cars and car parts the best bet would be a car table, a parts table, and a modifications table with a compound foreign key of car_id and part_id. That _theoretically_ should give you a time-specific snapshot of parts and mods per car.

[NotionCommotion]

 

19 hours ago, kicken said:

If every data source gets it's own unique gateway, then you could instead link the source to the gateway then link the gateway to the device.

Yes, each GatewayDataSource gets its own unique gateway, and "linking" as you say was my intention. Would you agree this is a one-to-one relationship between source and gateway, and gateway to device?  Is there some de facto standard where to locate the FK for these two links, or are there application specific details one must consider?  For instance, to quote Zane, "Cars contain a motor.  Motors do not contain cars.  Therefore, put a column for motor in the Cars table and link through the various motor ids" makes sense, however, if some app was all about artificial brains, it might make more sense that a brain has a host (aka body), and one should do differently.  PS.  No, I am not creating a brain and maybe I should strike this obscure example! 

19 hours ago, kicken said:

This is the part of your post that is causing me issues trying to understand what kind of setup you have and thus how it should be structured.

This implies to me your devices (and maybe gateways) are some kind of shared resource meaning many gateways may link to the same device so if a gateway is deleted it shouldn't be deleting the device as it might still be used by others (now or later).

It might be helpful for explaining the situation to focus more on explaining how the physical networks your trying interact with work and what you need to do with them, rather than or in addition to the code your trying to write.

• Modbus uses an 8 bit address to identify a device on the network and 16 bit registrars to locate some specific data in the device.
• Bacnet uses a 32 bit value to identify the device and another 32 bit value to represent something in the device (it actually uses ID and type, but I think this can be disregarded for this discussion).

These 3rd party modbus and bacnet devices are not managed by my application and their associated identifying addresses are set outside of the application.  A 3rd party modbus device is interfaced to by one of my ModbusGateways and a 3rd party bacnet device is similarly interfaced to by one of my BacnetGateways.  Since ModbusGateways and BacnetGateways are also modbus devices and bacnet devices respectively and there are requirements to keep addresses for both unique on a given network, I created these ModbusDevice and BacnetDevice entities/tables which are used for both 3rd party devices and gateways alike.  A device associated with a gateway can be deleted by the user and removed from the database, however, the devices in my application associated with some 3rd party controller should not be deleted from the database but only tagged as deleted.  If a deleted 3rd party device is later added back as identified by its unique address, then the same PK associated with the specific data points in the device must be used as it is also used as a natural key by a historian.

Hope I didn't totally confuse you (I certain confused myself  )  Thank you

[NotionCommotion]

Thanks Zane and maxxd,

I am trying to keep track of user provided configuration settings of something.  I might be wrong, but it seems like it could be just about anything, so I used cars and motors as an example, but did a poor job communicating that they were user settings.  Keeping with my car example, user settings might be current mileage, serial number, color (not necessarily from the factory), and even spark plug torque (assuming cars still have spark plugs). The point is that the user provides settings for their particular something and these settings are saved (latest settings only and no audits required) in the DB.  No issue yet and I have done this often.

But then there are multiple variations of this something which require their own class and these classes have some properties common to all of them, but have other properties unique to their particular variation.  In the past, I've used inheritance, but I am now trying to default to injection when possible, and thus created a single class which contains all the common properties and then inject some small object which represents the other properties unique to each particular variation.  Data associated with each instance of this common object as well as the injected object is random user provided data, and therefore it seemed to me that I needed some one-to-one relationship between the two.  Agree?

On 11/4/2019 at 5:58 PM, NotionCommotion said:

What reasoning should one use to determine whether the Car table contain the Motor ID or should the Motor table contain the Car ID?

Ditching the car/motor analogy, the same question is "If a common object contains a single unique specific object, what reasoning should one use to determine whether the common table should contain specific entity ID or whether the specific table should contain the common entity ID?" 

Thanks

[kicken]

4 hours ago, NotionCommotion said:

Ditching the car/motor analogy, the same question is "If a common object contains a single unique specific object, what reasoning should one use to determine whether the common table should contain specific entity ID or whether the specific table should contain the common entity ID?" 

In general I'd say your common object should only contain the common properties.  As such, your common object shouldn't contain any references to some other item-specific details.  The reference to the common object should either be in the specific details or some linking table. 

Another way to look at it is, if your relationship wasn't one:one then you'd have to reference the common object from inside the specific object.  Since in the one:one you could technically do it either way, might as well do it the same way in both cases for a) Consistency and b) Makes a one:one to one:many conversion later easier.

So from what I understand of your gateway/devices situation, you'd end up with something like this:

PhysicalDevice represents your Modbus/Bacnet/whatever devices that are pre-generated.  These rows are essentially static and never changed.

Then you have your Datasource, gateway, device one-to-one relationships connecting those all together.  If your device needs to be using one of the physical devices you set PhysicalDeviceId, otherwise leave it null.  Or if needed, break out the physical device mapping to yet another table.

If you need to determine which physical devices are being used, just check if there is any reference to them in the Device table.

Not sure if any of that will help with your real situation or not.

[Zane]

So, it sounds like what you're after is custom attributes

User A has a hypnotism rate of 45 neurons per second, and a clairvoyance rating of 5 / 10.

User B has a rank of 3 of understanding of quantum physics and a shoe size of 25 and a half meters.

User C has fourteen years worth of towel folding experience

User D can eat twenty three beer battered brauttwerst in 48 seconds.

Am I getting closer to what you're talking about?

[NotionCommotion]

Thank you Kicken,  Your understanding is for the most part accurate.   Did I correctly capture the reasons why you said "In general I'd say your common object should only contain the common properties"?

• Requires table inheritance if given common object is injected with specific object of different class/table or there is type of common object which doesn't require an injected object.
• Consistent with one:many
• Can easier be convert to a one:many

[NotionCommotion]

3 minutes ago, Zane said:

So, it sounds like what you're after is custom attributes

I've never head the term "custom attributes", but suppose I do so.

User A is named Bob,  is 5 feet tall and 38 years old, and has special skills: has a hypnotism rate of 45 neurons per second, and a clairvoyance rating of 5 / 10.

User B is named Jill, is 6 feet tall and 28 years old, and has special skills: has a rank of 3 of understanding of quantum physics and a shoe size of 25 and a half meters.

User C is named Tom,  is 4 feet tall and 12 years old, and has special skills: has fourteen years worth of towel folding experience

User D is named Jane, is 5 feet tall and 18 years old, and has special skills: can eat twenty three beer battered brauttwerst in 48 seconds.