DB's table names and primary keys
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
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.
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
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
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
Maybe the column
type could be renamed to
currency and defined as FK?
With regard to the table renaming, I also prefer
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
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.
Deleting a branch is permanent. It CANNOT be undone. Continue?