Separate table to store long string identifiers



  • 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.


  • QA Engineer

    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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2