Create table as I need it, how to reference in query?  
Author Message
StephenEddie





PostPosted: Thu Mar 09 15:47:08 CST 2006 Top

SQL Server Developer >> Create table as I need it, how to reference in query?

I'll try to use the customer/orders example for my situation.

Let's say I have 10 customers and each customer has n order records. Most
of the time I will just accept the default order of the order records. In
other words, when I SELECT a customers orders I get them back in the order
they are in the table.

In some cases (remember, I'm not really dealing with customers/orders) I
want to explicitly re-order a customer's order records. Not by any
particular field. There is no logical pattern.

I was thinking that I could maintain a Customer_Orders_Order table. It
would look something like this:
[ CustomerID (FK to the customer tbl) ] [ OrderIndex ] [ OrderID (FK to
Order tbl) ]


the values might look like:
1 1 2
1 2 5
1 3 8
1 4 9
2 1 2
2 2 4
2 3 3
2 4 1
2 5 5

I would use this table to display the orders in the desired order in the UI.
OrderIndex would be the index in a ListView or DataGrid, etc.

My question is, would you store all the information in a single table or
would you create a table per customer to store this information and only if
the Customer's orders have actually been reordered from their default
presentation order (row order in the table)?

If your answer is create a table for each customer, how do you work with a
dynamically created table in a sproc or query? If my client app detects
that a user dragged an order above another order thus changing the
presentation order, I would create a table with a unique name, maybe
Tbl<customerName>_OrderPresentationOrdering. Then I could insert the
ordering information. Now, later when I want to display this customer's
orders and want to check if there is explicit presentation ordering that I
should use, how do I query the table I just created? Assuming I know the
naming convention used, can you concatenate a string in a sproc to build a
table name to be used in a Query?

Man, I hope this makes sense to someone, it's hard to explain.. :)

Thanks for any help,
Steve

SQL Server133  
 
 
Steve





PostPosted: Thu Mar 09 15:47:08 CST 2006 Top

SQL Server Developer >> Create table as I need it, how to reference in query? I was just thinking about this and realized that updates and inserts would
be involved due to the quantity of item in the update/insert. This got me
thinking... If I store this data per customer, inside the customer record,
then I don't need to have anything other than an array of OrderIDs.

So I can just have a delimited string stored in a field called
"OrderOrdering" (stupid name, my actual name would be "ProtocolOrdering" but
sticking with the example.....)
something like:
4;2;7;1;3;5

Insert and updates are easy (fast) and there really is no need to normalize
data like this, is there? I won't use it for searches or anything.

If anyone has a compelling reason why I shouldn't do this, please share,
please, please.




>
> I'll try to use the customer/orders example for my situation.
>
> Let's say I have 10 customers and each customer has n order records. Most
> of the time I will just accept the default order of the order records. In
> other words, when I SELECT a customers orders I get them back in the order
> they are in the table.
>
> In some cases (remember, I'm not really dealing with customers/orders) I
> want to explicitly re-order a customer's order records. Not by any
> particular field. There is no logical pattern.
>
> I was thinking that I could maintain a Customer_Orders_Order table. It
> would look something like this:
> [ CustomerID (FK to the customer tbl) ] [ OrderIndex ] [ OrderID (FK
> to Order tbl) ]
>
>
> the values might look like:
> 1 1 2
> 1 2 5
> 1 3 8
> 1 4 9
> 2 1 2
> 2 2 4
> 2 3 3
> 2 4 1
> 2 5 5
>
> I would use this table to display the orders in the desired order in the
> UI. OrderIndex would be the index in a ListView or DataGrid, etc.
>
> My question is, would you store all the information in a single table or
> would you create a table per customer to store this information and only
> if the Customer's orders have actually been reordered from their default
> presentation order (row order in the table)?
>
> If your answer is create a table for each customer, how do you work with a
> dynamically created table in a sproc or query? If my client app detects
> that a user dragged an order above another order thus changing the
> presentation order, I would create a table with a unique name, maybe
> Tbl<customerName>_OrderPresentationOrdering. Then I could insert the
> ordering information. Now, later when I want to display this customer's
> orders and want to check if there is explicit presentation ordering that I
> should use, how do I query the table I just created? Assuming I know the
> naming convention used, can you concatenate a string in a sproc to build a
> table name to be used in a Query?
>
> Man, I hope this makes sense to someone, it's hard to explain.. :)
>
> Thanks for any help,
> Steve
>


 
 
Alan





PostPosted: Thu Mar 09 16:28:25 CST 2006 Top

SQL Server Developer >> Create table as I need it, how to reference in query? I have a feeling you're about to feel the wrath of JC.

But to answer your question, you would create a single table for this.
I see what you mean by your sorting information. In that case, I
consider that type of data to not have anything to do with the database
-- while it's human readable and client readable, I consider the data
to be arbitrary to anything accessing it within the database. It might
as well be random binary data for all your database is concerned. Now,
the question is, does this data belong in your database? You're
breaking encapsulation by storing this in your database if it's not
specific to the data. I make two exceptions to putting
application-specific data within a database. The first is when the
database is used by one and only one application, and will be used by
one and only one application for the purpose of storing data and logic
specific to that application. The second is when the performance or
implementation advantages of storing such application-specific logic
and data are exceptionally significant.

-Alan

 
 
--CELKO--





PostPosted: Thu Mar 09 16:36:13 CST 2006 Top

SQL Server Developer >> Create table as I need it, how to reference in query? >> My question is, would you store all the information in a single table or
would you create a table per customer to store this information and
only if
the Customer's orders have actually been reordered from their default
presentation order (row order in the table)? <<

Ask yourself the most basic relational questions. Are customer orders
all things of the same kind logically? YES!! That means they go into
one table, which models that set. You put Britney Spears and Squid in
their own tables, not customers.

>> when I want to display this customer's orders and want to check if there is explicit presentation ordering that I should use, how do I query the table I just created? <<

Presentation order should not be part of the schema. Again, ask
yourself the most basic question about a tiered architecture. Where is
formatting and presentation done? Answer: in the front end and never
in the database.

Now, havign said that, if this "sequencing column" has some logical
meaning in the data model you can put it in the table.

 
 
David





PostPosted: Thu Mar 09 16:38:34 CST 2006 Top

SQL Server Developer >> Create table as I need it, how to reference in query?
> I was just thinking about this and realized that updates and inserts would
> be involved due to the quantity of item in the update/insert. This got me
> thinking... If I store this data per customer, inside the customer record,
> then I don't need to have anything other than an array of OrderIDs.
>
> So I can just have a delimited string stored in a field called
> "OrderOrdering" (stupid name, my actual name would be "ProtocolOrdering" but
> sticking with the example.....)
> something like:
> 4;2;7;1;3;5
>
> Insert and updates are easy (fast) and there really is no need to normalize
> data like this, is there? I won't use it for searches or anything.
>
> If anyone has a compelling reason why I shouldn't do this, please share,
> please, please.
>
>

What reason would you have for NOT normalizing in this case?

You seem to be making something very simple into something very
complex. No question in my mind: One table in Normal Form. The keys
would be I assume (customer, order_index) and (customer, order_id).

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

 
 
Steve





PostPosted: Thu Mar 09 16:37:42 CST 2006 Top

SQL Server Developer >> Create table as I need it, how to reference in query? I've upset JC?
(who is JC? Like Jesus Christ JC or is there a NG regular named JC?)


Yes, the data does belong to the database. I'll tell you why; given my
example, it sounds like an app preference or user preference, but this
ordering is actually used to generate source code. If a user logs in at
location A and initiates a build without knowing the ordering of the
records, that would result in invalid firmware and an irritated customer.

This is a feature I should have added from the get go, but I overlooked it.
So moving forward and agreeing (I assume we agree?) that the data does
belong in the DB, is this a valid case for a delimited string?

If not, make your case. I want to learn the right way.

Thanks,
Steve




>I have a feeling you're about to feel the wrath of JC.
>
> But to answer your question, you would create a single table for this.
> I see what you mean by your sorting information. In that case, I
> consider that type of data to not have anything to do with the database
> -- while it's human readable and client readable, I consider the data
> to be arbitrary to anything accessing it within the database. It might
> as well be random binary data for all your database is concerned. Now,
> the question is, does this data belong in your database? You're
> breaking encapsulation by storing this in your database if it's not
> specific to the data. I make two exceptions to putting
> application-specific data within a database. The first is when the
> database is used by one and only one application, and will be used by
> one and only one application for the purpose of storing data and logic
> specific to that application. The second is when the performance or
> implementation advantages of storing such application-specific logic
> and data are exceptionally significant.
>
> -Alan
>


 
 
--CELKO--





PostPosted: Thu Mar 09 16:42:40 CST 2006 Top

SQL Server Developer >> Create table as I need it, how to reference in query? >> So I can just have a delimited string stored in a field [sic] called"OrderOrdering" (stupid name, my actual name would be "ProtocolOrdering" but sticking with the example.....) something like:4;2;7;1;3;5 <<

Once more, you missed the most basic concepts again! Look up "First
Normal Form" (1NF), and learn why a column is not a field, a row is not
a record and a table is not a file. You might want to read at least
one book on RDBMS before you code in SQL.

 
 
Alan





PostPosted: Thu Mar 09 16:50:27 CST 2006 Top

SQL Server Developer >> Create table as I need it, how to reference in query? >I've upset JC?
>(who is JC? Like Jesus Christ JC or is there a NG regular named JC?)

I think you've probably figured this out by now.

So, you've decided to keep this in your database. Be aware of the
possible future implications of this. If you store multiple values a
single row, those values will be utterly useless in your database and
you'll be forced to do your sorting on the client. Next, if you dump
your front-end and it's rewritten, you'll have this artifact in your
database that those inheriting your work won't know what to do with
(or, given enough time, you won't either). In this situation, where
you've decided to store application-specific data in your database, at
least make it friendly to whoever may be administering thing. Store
your application-specific data in an application-specific location --
either a "helper" database that is specific to your application, or in
tables that are clearly indicated as being specific to your application
(e.g. App_MyApplication_InvoiceSorting) so that it's known they can be
dropped without implications once your application is no longer used.

-Alan

 
 
--CELKO--





PostPosted: Thu Mar 09 16:52:39 CST 2006 Top

SQL Server Developer >> Create table as I need it, how to reference in query? >> 've upset JC? (who is JC? Like Jesus Christ JC or is there a NG regular named JC?) <<

This si why I go by "--CELKO--" in the Newsgroup; peopel keep getting
us confused all the time.

>> Yes, the data does belong to the database. I'll tell you why; given my example, it sounds like an app preference or user preference, but this ordering is actually used to generate source code. If a user logs in at location A and initiates a build without knowing the ordering of the records [sic], that would result in invalid firmware and an irritated customer. <<

So the ordering has logical meanng in the data model!

>> This is a feature I should have added from the get go, but I overlooked it.
So moving forward and agreeing (I assume we agree?) that the data does
belong in the DB, is this a valid case for a delimited string? <<

NO, it is a valid reason for a sequencing column in First Normal Form.
Since you will not post any DDL or even helpful narrative with
meaningful names, here is a guess:

CREATE TABLE FirmwareTemplates
(customer_id INTEGER NOT NULL
REFERENCES Custromers (customer_id),
template_seq INTEGER NOT NULL,
template_txt VARCHAR (255) NOT NULL,
PRIMARY KEY (customer_id, template_seq ));

 
 
Steve





PostPosted: Thu Mar 09 17:21:21 CST 2006 Top

SQL Server Developer >> Create table as I need it, how to reference in query? LOL.. let the NG lashings begin!

My entire database is application specific to a single app. It's entire
reason for existence is my application. It would seem in that case that ALL
my data is application specific. Is that bad? I'm getting the distinct
feeling that is somehow a bad thing?

Either way, yes, I am doing the sorting on the client. This is intentional.

My application is a high level firmware editor for our sales people. We
sell a product that can be customized (a bit) per customer. However, ALL
device's firmware must share some data that is constant.

One of the things that can be customized is the ordering of menu items in a
UI menu. All devices have the same menu items, but some customers want them
in a different order. I have approached this by maintaing a base shared
collection of menu items that all customers use. When they want to reorder
the items I store "meta data" (I'm not even sure if I understand that term,
seems like "settings" to me, but whatever) in their record.

Point is, I don't return a separate set of ordered menu item records for
each customer, I get the menu items for all customers, then the ordering
data for each customer, then on the client each customer has a reference to
the shared items and I order them the way they want in the UI.

Hope that makes a bit of sense.




> >I've upset JC?
>>(who is JC? Like Jesus Christ JC or is there a NG regular named JC?)
>
> I think you've probably figured this out by now.
>
> So, you've decided to keep this in your database. Be aware of the
> possible future implications of this. If you store multiple values a
> single row, those values will be utterly useless in your database and
> you'll be forced to do your sorting on the client. Next, if you dump
> your front-end and it's rewritten, you'll have this artifact in your
> database that those inheriting your work won't know what to do with
> (or, given enough time, you won't either). In this situation, where
> you've decided to store application-specific data in your database, at
> least make it friendly to whoever may be administering thing. Store
> your application-specific data in an application-specific location --
> either a "helper" database that is specific to your application, or in
> tables that are clearly indicated as being specific to your application
> (e.g. App_MyApplication_InvoiceSorting) so that it's known they can be
> dropped without implications once your application is no longer used.
>
> -Alan
>


 
 
Steve





PostPosted: Thu Mar 09 17:22:35 CST 2006 Top

SQL Server Developer >> Create table as I need it, how to reference in query?


>>> 've upset JC? (who is JC? Like Jesus Christ JC or is there a NG regular
>>> named JC?) <<
>
> This si why I go by "--CELKO--" in the Newsgroup; peopel keep getting
> us confused all the time.

>
>>> Yes, the data does belong to the database. I'll tell you why; given my
>>> example, it sounds like an app preference or user preference, but this
>>> ordering is actually used to generate source code. If a user logs in at
>>> location A and initiates a build without knowing the ordering of the
>>> records [sic], that would result in invalid firmware and an irritated
>>> customer. <<
>
> So the ordering has logical meanng in the data model!
yes, yes it does.


>
>>> This is a feature I should have added from the get go, but I overlooked
>>> it.
> So moving forward and agreeing (I assume we agree?) that the data does
> belong in the DB, is this a valid case for a delimited string? <<
>
> NO, it is a valid reason for a sequencing column in First Normal Form.
> Since you will not post any DDL or even helpful narrative with
> meaningful names, here is a guess:


>
> CREATE TABLE FirmwareTemplates
> (customer_id INTEGER NOT NULL
> REFERENCES Custromers (customer_id),
> template_seq INTEGER NOT NULL,
> template_txt VARCHAR (255) NOT NULL,
> PRIMARY KEY (customer_id, template_seq ));
>
Gotcha, I will investigate this, thanks for the suggestion!


 
 
Steve





PostPosted: Thu Mar 09 17:30:06 CST 2006 Top

SQL Server Developer >> Create table as I need it, how to reference in query?


>>> So I can just have a delimited string stored in a field [sic]
>>> called"OrderOrdering" (stupid name, my actual name would be
>>> "ProtocolOrdering" but sticking with the example.....) something
>>> like:4;2;7;1;3;5 <<
>
> Once more, you missed the most basic concepts again! Look up "First
> Normal Form" (1NF), and learn why a column is not a field, a row is not
> a record and a table is not a file. You might want to read at least
> one book on RDBMS before you code in SQL.
>


I've read a couple. Look, I'm not a full time DB guy, I'm not even a full
time programmer. So I don't have the luxury of learning ALL the "basics" as
you call them. I wish I did, I like to learn.
I will take form this thread that;
a) I'm a drolling idiot ;)
b) I should not use a delimited string in a field or cell or shit, whatever
it is called.

On the other hand, I am quite proud of what I have accomplished with my
system here. It's far from perfect or even good, but it does work well. I
would LOVE it if you ever wanted to accept a zip of the whole app (1.5MB)
and solution and really tell me where I blew it. I'm eager to make it
better and will start by using a table for my ordering instead of a
delimited string.

Thanks for the guidance and info.

-SK


 
 
05ponyGT





PostPosted: Thu Mar 09 20:05:35 CST 2006 Top

SQL Server Developer >> Create table as I need it, how to reference in query?

>.
> I should not use a delimited string in a field or cell or shit, whatever
> it is called.

There's something extra-special when speaking from ones heart.
Well done!:)


 
 
Jim





PostPosted: Fri Mar 10 08:46:30 CST 2006 Top

SQL Server Developer >> Create table as I need it, how to reference in query? The way you describe it here sounds like you are on the right track. I
think most of the criticism was due to not understanding what you were
trying to do. This is mainly because you used an intentionally vague
description of what you were trying to do.

What you are doing is creating a tree of sorts (although you may or may not
have implemented it as such). Do a search on the web and you will find many
articles talking about how to do this. Keep in mind that it adds a fair
level of complexity that will cause most programmers migraines, but isn't
all that bad once you get used to it. It seems to be the way this sort of
thing is done. In all honestly, you are probably better off reposting this
question specifically as to how to implement a tree menu structure (you
could reorder menus from left to right as well as reordering menu items top
to bottom).

If you keep it simple and only allow the items within a given menu to be
moved up or down, the original suggestion of having a table with ClientID,
Sequence, MenuItemID will work, and is halfway to a real tree structure.
Storing the ordering information in a single field populated by the
application will also work, although it technically goes against database
design principles. For your purposes I don't think it makes too much
difference NOW, except that if the application (and this function) grows and
expands, you will eventually need the full blown tree functionality anyway.
So, although either approach will handle the immediate problem, creating a
tree will give you a more complete and flexible solution.

I know Joe Celko has several articles on tree structures in SQL (perhaps a
book as well?). You can also find links to articles if you search these
forums (an excellent one was posted in the last week or two). I don't have
any links to in depth articles, unfortunately, but they should be easy
enough to add.





> LOL.. let the NG lashings begin!
>
> My entire database is application specific to a single app. It's entire
> reason for existence is my application. It would seem in that case that
ALL
> my data is application specific. Is that bad? I'm getting the distinct
> feeling that is somehow a bad thing?
>
> Either way, yes, I am doing the sorting on the client. This is
intentional.
>
> My application is a high level firmware editor for our sales people. We
> sell a product that can be customized (a bit) per customer. However, ALL
> device's firmware must share some data that is constant.
>
> One of the things that can be customized is the ordering of menu items in
a
> UI menu. All devices have the same menu items, but some customers want
them
> in a different order. I have approached this by maintaing a base shared
> collection of menu items that all customers use. When they want to
reorder
> the items I store "meta data" (I'm not even sure if I understand that
term,
> seems like "settings" to me, but whatever) in their record.
>
> Point is, I don't return a separate set of ordered menu item records for
> each customer, I get the menu items for all customers, then the ordering
> data for each customer, then on the client each customer has a reference
to
> the shared items and I order them the way they want in the UI.
>
> Hope that makes a bit of sense.
>
>


> > >I've upset JC?
> >>(who is JC? Like Jesus Christ JC or is there a NG regular named JC?)
> >
> > I think you've probably figured this out by now.
> >
> > So, you've decided to keep this in your database. Be aware of the
> > possible future implications of this. If you store multiple values a
> > single row, those values will be utterly useless in your database and
> > you'll be forced to do your sorting on the client. Next, if you dump
> > your front-end and it's rewritten, you'll have this artifact in your
> > database that those inheriting your work won't know what to do with
> > (or, given enough time, you won't either). In this situation, where
> > you've decided to store application-specific data in your database, at
> > least make it friendly to whoever may be administering thing. Store
> > your application-specific data in an application-specific location --
> > either a "helper" database that is specific to your application, or in
> > tables that are clearly indicated as being specific to your application
> > (e.g. App_MyApplication_InvoiceSorting) so that it's known they can be
> > dropped without implications once your application is no longer used.
> >
> > -Alan
> >
>
>