469,950 Members | 1,941 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,950 developers. It's quick & easy.

Is it fundamental limitation of MySQL?

I would like to create data model for a group of stores.
All stores in this group are very similar to each other, so it is
natural
to allocate one MySQL database per store. Each database is going to
have very similar set of tables with content related to a particular
store.

Let's suppose each store has multiple departments which are also very
similar to each other. In this case I would like to model the
departments
as MySQL databases. Still I would like to group them by the particular
store. Actually I would like to have a group of sub-databases inside
each database.

However, the MySQL database directory structure is essentially flat.
It has only one level of databases, although the file system allows
any number of subdirectories. Here we have MySQL limitation. How I can
model such a hierarchical table structures in MySQL? Should I emulate
the hierarchy with my own means?

For example I could use table names with prefixes or something like
that.
Of course, it would be easier for me if MySQL itself takes care of
this
hierarchy. What are the usual way to implement such a table hierarchy
in
MySQL?

Oracle has this capability with tablespaces. It would be nice if MySQL
could have this feature as well.

Any of your thoughts will be highly appreciated.

Best regards,

Jacob Nikom
Jul 19 '05 #1
6 3423
Jacob - are you referring to primary key / foreign key relationships ???
The things you've described can be set up rather easily in mysql if you
think of your subs as tables, and not 'databases' . There's a good book on
SQL from CJ Date - 'An Intro to SQL' - can implement most of the concepts
there in mySql through CODING . Oracle name spaces are fine, cool, yes -
but you can create most of the same portability from namespaces by
automating a lot of your scripting processing ..

lemme know your thoughts ..
mondo regards [Bill]

--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
FREE LONG DISTANCE -> mailto:ex********@efgroup.net
mySql / VFP / MS-SQL
"jacob nikom" <ja*********@ieee.org> wrote in message
news:6e**************************@posting.google.c om...
I would like to create data model for a group of stores.
All stores in this group are very similar to each other, so it is
natural
to allocate one MySQL database per store. Each database is going to
have very similar set of tables with content related to a particular
store.

Let's suppose each store has multiple departments which are also very
similar to each other. In this case I would like to model the
departments
as MySQL databases. Still I would like to group them by the particular
store. Actually I would like to have a group of sub-databases inside
each database.

However, the MySQL database directory structure is essentially flat.
It has only one level of databases, although the file system allows
any number of subdirectories. Here we have MySQL limitation. How I can
model such a hierarchical table structures in MySQL? Should I emulate
the hierarchy with my own means?

For example I could use table names with prefixes or something like
that.
Of course, it would be easier for me if MySQL itself takes care of
this
hierarchy. What are the usual way to implement such a table hierarchy
in
MySQL?

Oracle has this capability with tablespaces. It would be nice if MySQL
could have this feature as well.

Any of your thoughts will be highly appreciated.

Best regards,

Jacob Nikom

Jul 19 '05 #2
Jacob - are you referring to primary key / foreign key relationships ???
The things you've described can be set up rather easily in mysql if you
think of your subs as tables, and not 'databases' . There's a good book on
SQL from CJ Date - 'An Intro to SQL' - can implement most of the concepts
there in mySql through CODING . Oracle name spaces are fine, cool, yes -
but you can create most of the same portability from namespaces by
automating a lot of your scripting processing ..

lemme know your thoughts ..
mondo regards [Bill]

--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
FREE LONG DISTANCE -> mailto:ex********@efgroup.net
mySql / VFP / MS-SQL
"jacob nikom" <ja*********@ieee.org> wrote in message
news:6e**************************@posting.google.c om...
I would like to create data model for a group of stores.
All stores in this group are very similar to each other, so it is
natural
to allocate one MySQL database per store. Each database is going to
have very similar set of tables with content related to a particular
store.

Let's suppose each store has multiple departments which are also very
similar to each other. In this case I would like to model the
departments
as MySQL databases. Still I would like to group them by the particular
store. Actually I would like to have a group of sub-databases inside
each database.

However, the MySQL database directory structure is essentially flat.
It has only one level of databases, although the file system allows
any number of subdirectories. Here we have MySQL limitation. How I can
model such a hierarchical table structures in MySQL? Should I emulate
the hierarchy with my own means?

For example I could use table names with prefixes or something like
that.
Of course, it would be easier for me if MySQL itself takes care of
this
hierarchy. What are the usual way to implement such a table hierarchy
in
MySQL?

Oracle has this capability with tablespaces. It would be nice if MySQL
could have this feature as well.

Any of your thoughts will be highly appreciated.

Best regards,

Jacob Nikom

Jul 19 '05 #3
Jacob - also - when I want to set up what I call templates for a database
domain -
in mysql I create tables that hold some generic stuff, and specific stuff
[dept names, for example]
and scan through the tables, dynamically building and executing the sql
commands I need to create new instances of similar tables ... I've done it
with mySql, MS-SQL, and VFP - its interesting how it works ..

For this question ->
'What are the usual way to implement such a table hierarchy in MySQL?'
my answer is -
through coding combined with code , variables, and table definitions stored
as in tables ..

can't really be more specific than that - there are other examples of data
driving programming out there -
hth - mondo regards [Bill]
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
FREE LONG DISTANCE -> mailto:ex********@efgroup.net
mySql / VFP / MS-SQL
"jacob nikom" <ja*********@ieee.org> wrote in message
news:6e**************************@posting.google.c om...
I would like to create data model for a group of stores.
All stores in this group are very similar to each other, so it is
natural
to allocate one MySQL database per store. Each database is going to
have very similar set of tables with content related to a particular
store.

Let's suppose each store has multiple departments which are also very
similar to each other. In this case I would like to model the
departments
as MySQL databases. Still I would like to group them by the particular
store. Actually I would like to have a group of sub-databases inside
each database.

However, the MySQL database directory structure is essentially flat.
It has only one level of databases, although the file system allows
any number of subdirectories. Here we have MySQL limitation. How I can
model such a hierarchical table structures in MySQL? Should I emulate
the hierarchy with my own means?

For example I could use table names with prefixes or something like
that.
Of course, it would be easier for me if MySQL itself takes care of
this
hierarchy. What are the usual way to implement such a table hierarchy
in
MySQL?

Oracle has this capability with tablespaces. It would be nice if MySQL
could have this feature as well.

Any of your thoughts will be highly appreciated.

Best regards,

Jacob Nikom

Jul 19 '05 #4
Jacob - also - when I want to set up what I call templates for a database
domain -
in mysql I create tables that hold some generic stuff, and specific stuff
[dept names, for example]
and scan through the tables, dynamically building and executing the sql
commands I need to create new instances of similar tables ... I've done it
with mySql, MS-SQL, and VFP - its interesting how it works ..

For this question ->
'What are the usual way to implement such a table hierarchy in MySQL?'
my answer is -
through coding combined with code , variables, and table definitions stored
as in tables ..

can't really be more specific than that - there are other examples of data
driving programming out there -
hth - mondo regards [Bill]
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
FREE LONG DISTANCE -> mailto:ex********@efgroup.net
mySql / VFP / MS-SQL
"jacob nikom" <ja*********@ieee.org> wrote in message
news:6e**************************@posting.google.c om...
I would like to create data model for a group of stores.
All stores in this group are very similar to each other, so it is
natural
to allocate one MySQL database per store. Each database is going to
have very similar set of tables with content related to a particular
store.

Let's suppose each store has multiple departments which are also very
similar to each other. In this case I would like to model the
departments
as MySQL databases. Still I would like to group them by the particular
store. Actually I would like to have a group of sub-databases inside
each database.

However, the MySQL database directory structure is essentially flat.
It has only one level of databases, although the file system allows
any number of subdirectories. Here we have MySQL limitation. How I can
model such a hierarchical table structures in MySQL? Should I emulate
the hierarchy with my own means?

For example I could use table names with prefixes or something like
that.
Of course, it would be easier for me if MySQL itself takes care of
this
hierarchy. What are the usual way to implement such a table hierarchy
in
MySQL?

Oracle has this capability with tablespaces. It would be nice if MySQL
could have this feature as well.

Any of your thoughts will be highly appreciated.

Best regards,

Jacob Nikom

Jul 19 '05 #5
"swdev1" <ws**********@advmed.com> wrote in message news:<la***************@newssvr22.news.prodigy.com >...
Jacob - also - when I want to set up what I call templates for a database
domain -
in mysql I create tables that hold some generic stuff, and specific stuff
[dept names, for example]
and scan through the tables, dynamically building and executing the sql
commands I need to create new instances of similar tables ... I've done it
with mySql, MS-SQL, and VFP - its interesting how it works ..

For this question ->
'What are the usual way to implement such a table hierarchy in MySQL?'
my answer is -
through coding combined with code , variables, and table definitions stored
as in tables ..

can't really be more specific than that - there are other examples of data
driving programming out there -
hth - mondo regards [Bill]
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
FREE LONG DISTANCE -> mailto:ex********@efgroup.net
mySql / VFP / MS-SQL
"jacob nikom" <ja*********@ieee.org> wrote in message
news:6e**************************@posting.google.c om...
I would like to create data model for a group of stores.
All stores in this group are very similar to each other, so it is
natural
to allocate one MySQL database per store. Each database is going to
have very similar set of tables with content related to a particular
store.

Let's suppose each store has multiple departments which are also very
similar to each other. In this case I would like to model the
departments
as MySQL databases. Still I would like to group them by the particular
store. Actually I would like to have a group of sub-databases inside
each database.

However, the MySQL database directory structure is essentially flat.
It has only one level of databases, although the file system allows
any number of subdirectories. Here we have MySQL limitation. How I can
model such a hierarchical table structures in MySQL? Should I emulate
the hierarchy with my own means?

For example I could use table names with prefixes or something like
that.
Of course, it would be easier for me if MySQL itself takes care of
this
hierarchy. What are the usual way to implement such a table hierarchy
in
MySQL?

Oracle has this capability with tablespaces. It would be nice if MySQL
could have this feature as well.

Any of your thoughts will be highly appreciated.

Best regards,

Jacob Nikom


Hi Bill,

Thank you very much for your response. I used to create data models for
departments in the stores, but I had never modeled multiple stores. May
I ask your opinion about my proposed solution:

1. I am going to have one central table containing multiple store names
and their IDs - primary and foreign keys

2. Each foreign key from the central table will lead to the table corres-
ponding to the specific store, which is actually the table containing
store's departments. Each department record also has its foreign key
leading to the customer table

3. Customer record has a foreign key leading to the address table and so on.

The problem with this approach is that the knowledge, which table to use
in order to find the record with correct foreign key is embedded in the head
of database programmer. It does not exists in the database.

For example, if I would like to have two address tables, one for the customer
addresses and another for the store addresses, how do I know which table to
look for - department table or store addresses table? It means that I am going
to have multiple foreign keys inside one record pointing to the different
tables. I am not completely comfortable with this solution. Do you think
I am wrong?

Best regards,

Jacob Nikom
Jul 19 '05 #6
"swdev1" <ws**********@advmed.com> wrote in message news:<la***************@newssvr22.news.prodigy.com >...
Jacob - also - when I want to set up what I call templates for a database
domain -
in mysql I create tables that hold some generic stuff, and specific stuff
[dept names, for example]
and scan through the tables, dynamically building and executing the sql
commands I need to create new instances of similar tables ... I've done it
with mySql, MS-SQL, and VFP - its interesting how it works ..

For this question ->
'What are the usual way to implement such a table hierarchy in MySQL?'
my answer is -
through coding combined with code , variables, and table definitions stored
as in tables ..

can't really be more specific than that - there are other examples of data
driving programming out there -
hth - mondo regards [Bill]
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
FREE LONG DISTANCE -> mailto:ex********@efgroup.net
mySql / VFP / MS-SQL
"jacob nikom" <ja*********@ieee.org> wrote in message
news:6e**************************@posting.google.c om...
I would like to create data model for a group of stores.
All stores in this group are very similar to each other, so it is
natural
to allocate one MySQL database per store. Each database is going to
have very similar set of tables with content related to a particular
store.

Let's suppose each store has multiple departments which are also very
similar to each other. In this case I would like to model the
departments
as MySQL databases. Still I would like to group them by the particular
store. Actually I would like to have a group of sub-databases inside
each database.

However, the MySQL database directory structure is essentially flat.
It has only one level of databases, although the file system allows
any number of subdirectories. Here we have MySQL limitation. How I can
model such a hierarchical table structures in MySQL? Should I emulate
the hierarchy with my own means?

For example I could use table names with prefixes or something like
that.
Of course, it would be easier for me if MySQL itself takes care of
this
hierarchy. What are the usual way to implement such a table hierarchy
in
MySQL?

Oracle has this capability with tablespaces. It would be nice if MySQL
could have this feature as well.

Any of your thoughts will be highly appreciated.

Best regards,

Jacob Nikom


Hi Bill,

Thank you very much for your response. I used to create data models for
departments in the stores, but I had never modeled multiple stores. May
I ask your opinion about my proposed solution:

1. I am going to have one central table containing multiple store names
and their IDs - primary and foreign keys

2. Each foreign key from the central table will lead to the table corres-
ponding to the specific store, which is actually the table containing
store's departments. Each department record also has its foreign key
leading to the customer table

3. Customer record has a foreign key leading to the address table and so on.

The problem with this approach is that the knowledge, which table to use
in order to find the record with correct foreign key is embedded in the head
of database programmer. It does not exists in the database.

For example, if I would like to have two address tables, one for the customer
addresses and another for the store addresses, how do I know which table to
look for - department table or store addresses table? It means that I am going
to have multiple foreign keys inside one record pointing to the different
tables. I am not completely comfortable with this solution. Do you think
I am wrong?

Best regards,

Jacob Nikom
Jul 19 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

10 posts views Thread by Steve | last post: by
51 posts views Thread by Casper Bang | last post: by
reply views Thread by Daniel Rossi | last post: by
8 posts views Thread by Kyle Kolander | last post: by
19 posts views Thread by cover | last post: by
1 post views Thread by TT | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.