Why does using available_primary_key() as scope for a table result in this behaviour?

Question

Scenario:

1. Two tables, each using uint64_t as a primary key
2. Table A rows need to reference rows in Table B
   • Why? Relational database data duplication reasoning etc etc
3. Both table's primary keys are generated by calling available_primary_key()
4. Instead of using a classic foreign key in Table A, I'm using Table B's primary key as a scope for Table A
   • Why? Rather than storing the FK on each row, which would require RAM, I can use the scope to effectively group the rows.

Question 1: is the above approach sensible?

Question 2: when listing all the scopes on the Table A, using get scope <account> -t <Table A> why does the scope not return the number correctly?

1. scope is returning values of "" for ID 0 and "............1" for any ID above 0, where the number is always equal to the ID.

2. count always seems to return double the value of the actual rows

My assumption for #1 is because you would normally use an account name for the scope, even though you can get the uint64_t value of it, it's translating the number back to a name object.

Some outputs to explain the behaviour further:

Table B

{
  "rows": [{
      "id": 0,
      "type": "Create Match",
      "max_reward": "10.0000 ENT",
      "max_pay_outs": 10
    },{
      "id": 1,
      "type": "Validate Match",
      "max_reward": "10.0000 ENT",
      "max_pay_outs": 10
    },{
      "id": 2,
      "type": "View Match",
      "max_reward": "5.0000 ENT",
      "max_pay_outs": 10
    }
  ],
  "more": false
}

Table A (scoped to ID 2)

{
  "rows": [{
      "id": 0,
      "source": "gre1111111p3",
      "owner": "gre333333333",
      "current_pay_outs": 0,
      "rewards_paid": "0.0000 ENT"
    },{
      "id": 1,
      "source": "gre1111111p3",
      "owner": "gre333333333",
      "current_pay_outs": 0,
      "rewards_paid": "0.0000 ENT"
    }
  ],
  "more": false
}

Table A (scopes)

{
  "rows": [{
      "code": "gre1111111p1",
      "scope": "",
      "table": "rwdsacts",
      "payer": "gre1111111p1",
      "count": 2
    },{
      "code": "gre1111111p1",
      "scope": "............1",
      "table": "rwdsacts",
      "payer": "gre1111111p1",
      "count": 2
    },{
      "code": "gre1111111p1",
      "scope": "............2",
      "table": "rwdsacts",
      "payer": "gre1111111p1",
      "count": 4
    }
  ],
  "more": ""
}

Answer 1

Question 1: is the above approach sensible?

Looks good to me.

My assumption for #1 is because you would normally use an account name for the scope, even though you can get the uint64_t value of it, it's translating the number back to a name object.

Actually, the opposite is true. A scope must always be a uint64_t, so if you pass in a name, it will translate it to the corresponding uint64_t interpretation of that name.

scope is returning values of "" for ID 0 and "............1" for any ID above 0, where the number is always equal to the ID.

I think this is a result of the cleos system trying to return something in the format of an eosio::name, when the data stored is actually just a uint64_t. So if it is zero it is considered an empty string, and if it is just a number, then it is padded with dots. I would say that this is then unreliable, because if the scope of the table is, say, 7, you can't then see the number 7 as that can't be included in an valid eosio::name.

count always seems to return double the value of the actual rows

Because your table has secondary indices. If you have more than just the primary index, then the row count is doubled. This is described in a Github issue.