Schema Questions

[narges saleh <sn...@gmail.com>]

Hi All,

I would appreciate your feedback, for the following, in terms of
performance for both inserts and queries.

1) Which one of these patterns is preferable for the table design?
A- Have a fat table/cache with nested objects, e.g. person table with a
hashmap of addresses.
B- Have person and address tables separate and just link them via foreign
keys.

2) Which one of these patterns is preferable for primary keys?
A- Have a UUID + affinity key as the primary key
B- Have the keys spelled out + affinity key. For example, assume person
table, combination of age and name uniquely identifies a person, so the key
will be person-name, person-age, and org-id.
If I have a associative table joining persons and addresses (if address is
a separate object), then in case B, I will have to include three fields
from person and the id from the address table, as opposed to case A, where
I will have UUID + orgid + address id. Would having one less field buy me
much, as opposed to having the overhead of creating UUIDs?

thanks

Re: Schema Questions

[Evgenii Zhuravlev <e....@gmail.com>]

There is no way to define nested collection of addresses as SQL fields. The
problem is that there is no such types in JDBC, so, it just won't work. So,
if you want to use SQL, just have separate tables for these objects.




вт, 12 мая 2020 г. в 06:07, narges saleh <sn...@gmail.com>:

> Thanks Evgenii.
> My next two questions are, assuming I go with option 1.1:
> 1) How do I define these nested addresses via query entities, assuming,
> I'd use binaryobjects when inserting. There can be multiple primary
> addresses and secondary addresses. E.g., {john,{primary-address:[addr1,
> addr2], secondary-addess:[addr3, addr4, addr5]}}
> 2) Can I use SQL if I am filtering by person and then I want certain
> information in the addresses?  say I want all the primary addresses for
> john., or I want the cities for the primary addresses for John.
>
> thanks.
>
> On Mon, May 11, 2020 at 4:56 PM Evgenii Zhuravlev <
> e.zhuravlev.wk@gmail.com> wrote:
>
>> Hi,
>>
>> The main question here is how you want to use this data. Do you use SQL?
>>
>> 1) It depends on the use case. If you plan to access only a person object
>> without any filtering by addresses and you will always need the entire
>> object, it makes sense to have one big object. But in this case, you won't
>> be able to filter persons by addresses, since SQL doesn't work with
>> collections. So, if you want to use SQL, it definitely makes sense to use
>> the second approach.
>>
>> 2) Of course, if you already have unique ID for object, it makes sense to
>> use it as a key, there is no need to generate an additional field for this.
>>
>> Evgenii
>>
>> пн, 11 мая 2020 г. в 09:20, narges saleh <sn...@gmail.com>:
>>
>>> Hi All,
>>>
>>> I would appreciate your feedback, for the following, in terms of
>>> performance for both inserts and queries.
>>>
>>> 1) Which one of these patterns is preferable for the table design?
>>> A- Have a fat table/cache with nested objects, e.g. person table with a
>>> hashmap of addresses.
>>> B- Have person and address tables separate and just link them via
>>> foreign keys.
>>>
>>> 2) Which one of these patterns is preferable for primary keys?
>>> A- Have a UUID + affinity key as the primary key
>>> B- Have the keys spelled out + affinity key. For example, assume person
>>> table, combination of age and name uniquely identifies a person, so the key
>>> will be person-name, person-age, and org-id.
>>> If I have a associative table joining persons and addresses (if address
>>> is a separate object), then in case B, I will have to include three fields
>>> from person and the id from the address table, as opposed to case A, where
>>> I will have UUID + orgid + address id. Would having one less field buy me
>>> much, as opposed to having the overhead of creating UUIDs?
>>>
>>> thanks
>>>
>>>

Re: Schema Questions

[narges saleh <sn...@gmail.com>]

Thanks Evgenii.
My next two questions are, assuming I go with option 1.1:
1) How do I define these nested addresses via query entities, assuming, I'd
use binaryobjects when inserting. There can be multiple primary addresses
and secondary addresses. E.g., {john,{primary-address:[addr1, addr2],
secondary-addess:[addr3, addr4, addr5]}}
2) Can I use SQL if I am filtering by person and then I want certain
information in the addresses?  say I want all the primary addresses for
john., or I want the cities for the primary addresses for John.

thanks.

On Mon, May 11, 2020 at 4:56 PM Evgenii Zhuravlev <e....@gmail.com>
wrote:

> Hi,
>
> The main question here is how you want to use this data. Do you use SQL?
>
> 1) It depends on the use case. If you plan to access only a person object
> without any filtering by addresses and you will always need the entire
> object, it makes sense to have one big object. But in this case, you won't
> be able to filter persons by addresses, since SQL doesn't work with
> collections. So, if you want to use SQL, it definitely makes sense to use
> the second approach.
>
> 2) Of course, if you already have unique ID for object, it makes sense to
> use it as a key, there is no need to generate an additional field for this.
>
> Evgenii
>
> пн, 11 мая 2020 г. в 09:20, narges saleh <sn...@gmail.com>:
>
>> Hi All,
>>
>> I would appreciate your feedback, for the following, in terms of
>> performance for both inserts and queries.
>>
>> 1) Which one of these patterns is preferable for the table design?
>> A- Have a fat table/cache with nested objects, e.g. person table with a
>> hashmap of addresses.
>> B- Have person and address tables separate and just link them via foreign
>> keys.
>>
>> 2) Which one of these patterns is preferable for primary keys?
>> A- Have a UUID + affinity key as the primary key
>> B- Have the keys spelled out + affinity key. For example, assume person
>> table, combination of age and name uniquely identifies a person, so the key
>> will be person-name, person-age, and org-id.
>> If I have a associative table joining persons and addresses (if address
>> is a separate object), then in case B, I will have to include three fields
>> from person and the id from the address table, as opposed to case A, where
>> I will have UUID + orgid + address id. Would having one less field buy me
>> much, as opposed to having the overhead of creating UUIDs?
>>
>> thanks
>>
>>

Re: Schema Questions

[Evgenii Zhuravlev <e....@gmail.com>]

Hi,

The main question here is how you want to use this data. Do you use SQL?

1) It depends on the use case. If you plan to access only a person object
without any filtering by addresses and you will always need the entire
object, it makes sense to have one big object. But in this case, you won't
be able to filter persons by addresses, since SQL doesn't work with
collections. So, if you want to use SQL, it definitely makes sense to use
the second approach.

2) Of course, if you already have unique ID for object, it makes sense to
use it as a key, there is no need to generate an additional field for this.

Evgenii

пн, 11 мая 2020 г. в 09:20, narges saleh <sn...@gmail.com>:

> Hi All,
>
> I would appreciate your feedback, for the following, in terms of
> performance for both inserts and queries.
>
> 1) Which one of these patterns is preferable for the table design?
> A- Have a fat table/cache with nested objects, e.g. person table with a
> hashmap of addresses.
> B- Have person and address tables separate and just link them via foreign
> keys.
>
> 2) Which one of these patterns is preferable for primary keys?
> A- Have a UUID + affinity key as the primary key
> B- Have the keys spelled out + affinity key. For example, assume person
> table, combination of age and name uniquely identifies a person, so the key
> will be person-name, person-age, and org-id.
> If I have a associative table joining persons and addresses (if address is
> a separate object), then in case B, I will have to include three fields
> from person and the id from the address table, as opposed to case A, where
> I will have UUID + orgid + address id. Would having one less field buy me
> much, as opposed to having the overhead of creating UUIDs?
>
> thanks
>
>