DB's table names and primary keys #18

Closed
opened 7 months ago by brickedsalamander · 3 comments
Collaborator

The definition of the table address defines the address itself as the primary key (named address_id). This may cause conflicts if other currencies are added to isyc. The balances of a forked blockcain can differ for one address. They are stored in different blockchains and are referenced by a different currency symbol but as the PK needs to be unique, only one balance could be stored in the current address table. Another example is the use of the same address on different EVM chains as discussed in #8.

A possible solution would be to define a composite PK based on the address and the type column. The use of an integer primary key should be condistered as an alternative.

In the current DB setup, the naming of currencies is inconsistent (see #17). I want to disuss if a integer primary keys would have advantages over the use of the currency symbol to uniquely identify currencies and blockchains.

Furthermore I propose renaming the table price to currencies or cryptos or similar. For me, it makes much sense to have such a table in the DB even if it holds the same data for now. The possible use of the primary key of this table when it is used to JOIN other tables would improve readability of SQL queries with one of those table names. This may seam to be a useless change, but if it is assumed to be the better solution, an early change in course of the possible above modifications may be better than having to modify the definition of a large DB.

The definition of the table `address` defines the address itself as the primary key (named `address_id`). This may cause conflicts if other currencies are added to isyc. The balances of a forked blockcain can differ for one address. They are stored in different blockchains and are referenced by a different currency symbol but as the PK needs to be unique, only one balance could be stored in the current `address` table. Another example is the use of the same address on different EVM chains as discussed in #8. A possible solution would be to define a composite PK based on the address and the `type` column. The use of an integer primary key should be condistered as an alternative. In the current DB setup, the naming of currencies is inconsistent (see #17). I want to disuss if a integer primary keys would have advantages over the use of the currency symbol to uniquely identify currencies and blockchains. Furthermore I propose renaming the table `price` to `currencies` or `cryptos` or similar. For me, it makes much sense to have such a table in the DB even if it holds the same data for now. The possible use of the primary key of this table when it is used to JOIN other tables would improve readability of SQL queries with one of those table names. This may seam to be a useless change, but if it is assumed to be the better solution, an early change in course of the possible above modifications may be better than having to modify the definition of a large DB.
Owner

Using the address itself as the PK is fine for now, but as you say if we aim to scale to other crytpocurrencies which some might be forks (thus, sharing addresses) we should change it.

I think that the solution I like the most is using a combination of the address plus the type, as this would not add any other value to the table. Using an integer identificator does not make too much sense to me, as an address, in combination of the type, should be a unique identifier (addresses do not repeat when on the same blockchain).

Which advantages would have using an integer identificator over the combination of the address and the type?

Refering to the database price table renaming, tables should be named always in singular as per my preference. Also there seems to be some sort of consensus on this (see this stackoverflow discussion). So if we change it, we should name it currency or crypto. I don't like the crypto as is too generic; this table stores the price for a certain currency for now and should not address anything related to 'crypto' as is. For this, if we rename it, and I like the proposal, I prefer using currency over crypto.

Using the address itself as the PK is fine for now, but as you say if we aim to scale to other crytpocurrencies which some might be forks (thus, sharing addresses) we should change it. I think that the solution I like the most is using a combination of the address plus the type, as this would not add any other value to the table. Using an integer identificator does not make too much sense to me, as an address, in combination of the type, should be a unique identifier (addresses do not repeat when on the same blockchain). Which advantages would have using an integer identificator over the combination of the address and the type? Refering to the database `price` table renaming, tables should be named always in singular as per my preference. Also there seems to be some sort of consensus on this ([see this stackoverflow discussion](https://stackoverflow.com/questions/338156/table-naming-dilemma-singular-vs-plural-names)). So if we change it, we should name it `currency` or `crypto`. I don't like the `crypto` as is too generic; this table stores the `price` for a certain currency for now and should not address anything related to 'crypto' as is. For this, if we rename it, and I like the proposal, I prefer using `currency` over `crypto`.
pluja added the
Kind: Enhancement
Status: Needs feedback
labels 7 months ago
brickedsalamander changed title from DB's talbe names and primary keys to DB's table names and primary keys 7 months ago
Poster
Collaborator

I thought an integer PK may increase performance but I've no experience with performance critical DBs.
I'm fine with a composed PK of address_id and type.

Maybe the column type could be renamed to currency and defined as FK?

With regard to the table renaming, I also prefer currency over crypto. I once came across the singular/plural discussion and noted the consesus of using singular, too. I missed that point in my proposal above.
So I'm fine with currency.

I thought an integer PK may increase performance but I've no experience with performance critical DBs. I'm fine with a composed PK of `address_id` and `type`. Maybe the column `type` could be renamed to `currency` and defined as FK? With regard to the table renaming, I also prefer `currency` over `crypto`. I once came across the singular/plural discussion and noted the consesus of using singular, too. I missed that point in my proposal above. So I'm fine with `currency`.
Owner

Maybe it would be worth it in terms of efficiency to use an integer ID, but maybe it would complicate things.

One of the reasons for using the address as PK was for simplification. I will look into using integer IDs.

Maybe it would be worth it in terms of efficiency to use an integer ID, but maybe it would complicate things. One of the reasons for using the address as PK was for simplification. I will look into using integer IDs.
pluja referenced this issue from a commit 7 months ago
pluja added
Status: Completed
and removed
Status: Needs feedback
labels 7 months ago
pluja closed this issue 7 months ago
Sign in to join this conversation.
No Milestone
No project
No Assignees
2 Participants
Notifications
Due Date

No due date set.

Dependencies

This issue currently doesn't have any dependencies.

Loading…
There is no content yet.