Using a value in a record of one table to identify which table to query?

Report · Feb 17, 2009

Hi,

I'm creating a site to be used by independent store owners belonging to a group. I have authentication code that will identify which store is logging in, and forward them to their appropriate page. That page then queries the database table containing a list of their customers. Easy enough, but I'm trying to automate it further. I'd like to just have ONE CF template that all logged-in stores are forwarded to, and have that CF template automatically query the appropriate table containing their customers. I'm aware you can't use a variable as a table name in a SQL query, but it seems like there should be some technique to accomplish this??

For example:
We have the "Stores" table. It contains three records, for: StoreA, StoreB, StoreC. Each store's record needs to be tied somehow to its corresponding customer's table. For instance:

StoreA's record is tied to table CustomersA, StoreB's record is tied to table CustomersB, etc.

When a store logs in, the application authenticates them using "username" and "password" data in the Stores table (hashed). The application then knows which store has logged in. It then needs to SELECT * FROM (appropriate Customers table).

So, if a user from StoreA logs in, the app identifies the user as StoreA. It then runs this query: SELECT * FROM CustomersA

Any thoughts?

Thanks,
Joe

Report · Feb 17, 2009

First of all, you can use a variable for a tablename in a query. Secondly, your job would be so much simpler with a normalized database.

Report · Feb 17, 2009

Well, with dynamic SQL you can (variable as table name), but not otherwise, right? I'm trying to avoid the dynamic SQL route, and since this isn't really a variable but a value already in another database record, I thought there might be a way to do it.

None of this is built yet so if there's an easier way to organize it I'd love to hear your thoughts. My thinking was that this IS a normalized database, since the only alternative I see is to put ALL customers' information into one table. That could wind up literally being thousands of records in one table, to be sorted every time the query is run. THAT seemed pretty poorly organized to me! Would it actually be better?

Thanks...

Report · Feb 17, 2009

Yes it would be better.

Just because you have lots of records doesn't mean you have to select them all. You just select the ones that belong to the store in question.

Report · Feb 18, 2009

StearmanDriver wrote:
> Well, with dynamic SQL you can (variable as table name), but not otherwise,
> right? I'm trying to avoid the dynamic SQL route, and since this isn't really
> a variable but a value already in another database record, I thought there
> might be a way to do it.
>
> None of this is built yet so if there's an easier way to organize it I'd love
> to hear your thoughts. My thinking was that this IS a normalized database,
> since the only alternative I see is to put ALL customers' information into one
> table. That could wind up literally being thousands of records in one table,
> to be sorted every time the query is run. THAT seemed pretty poorly organized
> to me! Would it actually be better?
>
> Thanks...
>

As Dan says, one table for one type of data is best. There is no need
to have multiple customer tables.

Thousands of records is not a big deal, honestly databases are known to
handle tables with millions and sometimes billions of records.

I work daily with tables that get ~2.5 million new records a year and it
has been around for 10 years now.

Report · Feb 18, 2009

Huh... well you're right, that does make things a LOT easier. It just seemed inefficient to me, but if it's standard practice then that's what I'll do.

Thanks!

Report · Feb 18, 2009

The efficiencies are not from breaking the data up into multiple tables.
But rather from properly designing this table to handle the data it is
expected to work with.

With the proper keys, indexes and sometimes even partitions an
enterprise database management system can easily handle tables with a
great deal of records in them.

Report · Feb 18, 2009

Before you get too deep into your project, you might take a gander at the book, Database Design for Mere Mortals. You might pick up a couple more hints.