[Discussion] db schema

[James McQuaid]

My MySQL experience is relatively recent and rather limited, having
used SQL Server and Oracle in employment, so I'm no guru.

"Is there a large enough difference in how you'd structure a scheme
for large scale vs small/speedy to justify thinking about this?"

Yes.  MySQL's most important feature is its storage-engine
architecture, which separates query processing and other server tasks
from data storage and retrieval.  MySQL 6.0 supports 13 different
runtime plug-in storage engines.  This allows one to determine on a
table by table basis how the data is stored and what performance
characteristics are sought.  Optimizing schema "can improve
performance by orders of magnitude". For high performance, schema and
indexes must be designed for specific queries. Performance
requirements must vary for different kinds of queries.  So, we should
allow a user to select one of several sets of schema combinations.
These sets of schema combinations might be based upon environmental
criteria such as hardware, home user vs. enterprise, etc.

Andre's point is very well taken with respect to requirements for data
retention and access: distributed file systems may be a necessity,
rather than an option, for regulated environments.

Jim


> Agreed. I don't want to get deep into the schema itself. Just want to
> explore the idea of not being tied to one schema.
>
> A question for the sql guru's though: Is there a large enough difference
> in how you'd structure a scheme for large scale vs small/speedy to
> justify thinking about this?
>
> Matt


>> On Tue, Oct 28, 2008 at 11:54 AM, Matt Jonkman <jonkman at jonkmans.com
>> <mailto:jonkman at jonkmans.com>> wrote:
>>
>>     I wonder if we ought to build several db schema's for different goals.
>>
>>     I'm not a deep-down sql expert (we'll get one involved when we get
>>     here), but I suspect if your goal is different a different schema will
>>     do best.
>>
>>     For instance, if you intend to keep every alert ever generated for
>>     historical comparison you need one schema to keep it snappy. If you
>>     intend to have a smaller number of alerts but a massive number of
>>     sensors inserting frequently then you may need a different structure.
>>
>>     So perhaps we should build several schemas and let you choose at setup.
>>     Then the engine just reads the version tag and inserts in that form.
>>
>>     Any sql guru's that could speak more to this?


> What is being discussed on this list in regards to technology and
> requirements for data rentetion and access just dont mesh well with
> current "standard RDBMS".  I would suggest looking at distributed file
> systems and some of the databases that sit on top of them (hypertable,
> hbase, etc come to mind).
>
> Andre Ludwig


>>>     I wonder if we ought to build several db schema's for different goals.
>>>
>>>     I'm not a deep-down sql expert (we'll get one involved when we get
>>>     here), but I suspect if your goal is different a different schema will
>>>     do best.
>>>
>>>     For instance, if you intend to keep every alert ever generated for
>>>     historical comparison you need one schema to keep it snappy. If you
>>>     intend to have a smaller number of alerts but a massive number of
>>>     sensors inserting frequently then you may need a different structure.
>>>
>>>     So perhaps we should build several schemas and let you choose at setup.
>>>     Then the engine just reads the version tag and inserts in that form.
>>>
>>>     Any sql guru's that could speak more to this?
>>>
>>>     Matt



-- 
James McQuaid
http://www.jamesmcquaid.com