Resource property inconsistencies

[James-Quigley]

It would be nice to update and enforce a standard going forward. Namely around things like:

• The id (uuid) of the row vs. the id (aws provided) of the resource. Usually the uuid is always called id, but the aws provided id sometimes is resource_id, sometimes it includes the name of the resource e.g. vpc_id.
• Foreign keys and pointers to other resources. If a FK exists, it almost always uses the id (uuid) of the target resource. But there are many places foreign keys aren't used, but an implicit reference to another resource exists, and the aws provided ID is used. If you do any manual joins, it can sometimes be confusing (mostly because of the above point) whether or not to point to id or vpc_id etc.

[yevgenypats]

That's a good point. I'd go with the following convention id (uuid) generated by PostgreSQL and if a resource contains an AWS id it will be named resource_id (instead of using a prefix of the table vpc_id) so we will always know what to look for when we are searching for an AWS ID. WDYT?

[James-Quigley]

That's a good point. I'd go with the following convention id (uuid) generated by PostgreSQL and if a resource contains an AWS id it will be named resource_id (instead of using a prefix of the table vpc_id) so we will always know what to look for when we are searching for an AWS ID. WDYT?

I'm fine with id and resource_id. I think it would be nice for foreign keys to always use resource_id instead of id

[roneli]

Yes, the current id system is a little flawed, but it was a simple workaround to release an initial SDK first.

There are a few complexities with making the FK point to the resource_id since some resources such as S3 bucket (don't have a unique ID and it's built with one or more fields)

Initially we added a solution in the SDK so you can decide what fields create a unique ID (i.e id = true in the column, and if non are stated auto Id is generated, usually used for embedded relation tables) making it more consistent, but we decided to leave it out for the first release. (since it had some problems without history support)

I see a few options here:

1. create a FK between vpc_id -> (vpc table) resource_id, but I am not sure if that will help with the confusion since FK only validate constraints.
2. use the solution I described above, creating a unique ID for the row based on tables "id" columns into a UUID. Every relation table that points to this table for example table A has vpc_id and table B (id == vpc_id), A's vpc_id will be hashed to be a unique id same as B's Id so A.vpc_id == B.id. This doesn't hurt the original implementation, but rather adds more control over how the SDK creates and handles the ids. There is one issue here the vpc_id field will either need to be duplicated in table A i.e vpc_id -> vpc_original_id, vpc_id (uuid) or vpc_id, vpc_uuid.

Tell me what do you think of the purposed options?

[James-Quigley]

Yes, the current id system is a little flawed, but it was a simple workaround to release an initial SDK first.

Totally understand

There are a few complexities with making the FK point to the resource_id since some resources such as S3 bucket (don't have a unique ID and it's built with one or more fields)

Though could you use bucket name since that is unique? Seems like every resource should have something that makes it unique.

Tell me what do you think of the purposed options?

I'm not sure I fully followed to be honest, but I generally get the complications.

Basically there are two headaches/inconsistencies I've been noticing.

1. the naming of the non-uuid, amazon provided id field. resource_id vs vpc_id as an example.
2. References to resources that aren't set up with foreign keys. e.g. a aws_ec2_nat_gateway has a vpc_id field, but it isn't a FK

With respect to 2, it seems that the only way to set up foreign keys at the moment is if you fetch a resource as a subresource. It would be nice to be able to specify a FK even if you didn't fetch in that manner.

[roneli]

@James-Quigley
Hi, just a quick update the new version of the SDK will let you define what are the primary keys of the table, as well as a change of id -> cq_id.
Hopefully this new update will reduce the naming confusion and not using the amazon or any provider's id field.
If a user won't define any primary keys it will work as the current version generating a cq_id as the primary key.

References to resources that aren't set up with foreign keys. e.g. a aws_ec2_nat_gateway has a vpc_id field, but it isn't a FK

about this, maybe in the future we will add for columns to add a reference cross resources, the only problem with this that it if you don't fetch vpcs than the vpc_id will say that the FK doesn't exist and cause an error. Unless, I missed the problem here fix me if I am incorrect.