| Re: Need help accessing the key array. [message #185461 is a reply to message #185454] |
Tue, 01 April 2014 20:32   |
Denis McMahon
Messages: 634
Registered: September 2010
Karma:
|
Senior Member |
|
|
On Mon, 31 Mar 2014 22:16:58 -0400, Richard Damon wrote:
> On 3/31/14, 8:55 PM, Denis McMahon wrote:
>> On Mon, 31 Mar 2014 07:37:19 -0400, Jerry Stuckle wrote:
>>
>>> I see no need for arrays or json objects in a database here. Or a
>>> graph database, for that matter.
>>
>> Agreed. A table with three elements per row, namely animal, colour,
>> rate,
>> keyed on the combination of animal and colour.
>>
>> Or possible 3 tables:
>>
>> animals: animal_name* | animal_id
>>
>> colours: colour* | colour_id
>>
>> rates: animal_id* | colour_id* | rate
>>
>> Where * indicates the field(s) that make the PK for the table, but I
>> suspect that's overkill for the task as described so far.
> I would probably make animal_id and colour_id the PK for those tables,
> as that is what other tables are going to refer to via Foreign Keys.
> animal_name and colour would be indexed, and possible unique.
I wouldn't include them at all given the scope of the problem as defined,
because they're clearly not needed. They may never become needed, if
"animal" is a unique identifier then there seems to be little benefit in
having a table simply to map a 1:1 relationship animal : animal_id such
that animal_id is used in many places.
The same goes for colour.
There may be a benefit in a large project in replacing strings with
integers in terms of memory and storage requirements, but in the scenario
described with 9 possible rates the overhead of the additional tables is
likely to cancel out the benefit.
It's also possible that on a 64 bit system, unless a high proportion of
both animals and colours when expressed as strings exceed 8 characters in
length, savings vary between marginal and none.
--
Denis McMahon, denismfmcmahon(at)gmail(dot)com
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]