1

In my postgres DB, have an assets and a sales table. The current owner of an asset is identified by an Ethereum address with 42 characters, e.g. 0x81238432CE9b51Fbc2b59AFD74Ba8D09863B8B00. A sale has a seller and a buyer, both identified with Ethereum addresses as well.

Instead of storing the Ethereum addresses in the assets table (owner_address), and the sales table (seller_address, buyer_address), I'm wondering if it's better to create an addresses table with an integer primary key and an address field with a unique constraint.

The assets and sales table would refer to the addresses via foreign keys. I still need to be able to make fast queries and fetch all assets owned, sold, or bought by a certain address.

I could save quite a bit of storage space, and I could store additional info with an address, e.g. a username (I'm storing the username in the assets table currently). On the other hand, inserting new assets and sales is more complicated. I would need to find out if the address already exists in the addresses table and refer to it, or create a new address.

Does it make sense to create a separate addresses table or am I just introducing unnecessary complexity? Storage space is not that big of a problem, but I'll have millions of assets and sales eventually...

An owner is identified by the address (the address is the owner). But an address/owner can own many assets, and be the buyer and seller of many assets. So there's a lot of address redundancy in my tables.

1
  • This is nothing to do with database normalization, either in the sense of putting into a 1NF or the sense of putting into higher NFs.
    – philipxy
    Commented Mar 23, 2022 at 11:56

2 Answers 2

1

This is a bit of an opinion based question, but if you have a natural primary key for an entity, you might as well use it. To avoid wasting space, you could convert the hexadecimal string to a number.

1
  • Postgres bytearray (bytea) with 20 bytes would be the best choice I think.
    – aimfeld
    Commented Mar 24, 2022 at 21:32
0

If these addresses can be reused by multiple owners, then this might make sense. You'd save some space by having only the integer id in the owner records, plus the table holding the integer id and each address, plus the overhead of indexes for getting to and from that other table.

However, if as I suspect the identifiers are unique to each owner, then there's nothing to be gained by splitting this value out into another table.

2
  • An owner is identified by the address (the address is the owner). But an address/owner can own many assets, and be the buyer and seller of many assets. So there's a lot of address redundancy in my tables.
    – aimfeld
    Commented Mar 23, 2022 at 9:51
  • OK, that makes a big difference. Storing just an 8-byte [big] integer value in all those other tables (instead of a 42 character field) could save you a /lot/ of space (especially with all the indexes that will be built on them!) It also allows the address to change over time (which may or may not be a "thing") /without/ causing major upheaval in your database every time it does.
    – Phill W.
    Commented Mar 23, 2022 at 9:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.