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,
SteveI 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.
"Steve" <sss@.sss.com> wrote in message
news:uThe317QGHA.6084@.TK2MSFTNGP10.phx.gbl...
> 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
>|||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|||Steve wrote:
> 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" b
ut
> 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 normaliz
e
> 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
--|||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
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1141943305.488882.121840@.u72g2000cwu.googlegroups.com...
>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
>|||>> So I can just have a delimited string stored in a field [sic] called"OrderOrde
ring" (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 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|||>> '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
all the time.hout knowing the ordering of the records [sic], that would result in invalid firm
ware and an irritated customer. <<
So the ordering has logical meanng in the data model!
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 ));|||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.
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1141944627.875280.107100@.i40g2000cwc.googlegroups.com...
> 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--" <jcelko212@.earthlink.net> wrote in message
news:1141944759.875765.197440@.e56g2000cwe.googlegroups.com...
> This si why I go by "--CELKO--" in the Newsgroup; peopel keep getting
> us
all the time.>
> So the ordering has logical meanng in the data model!
yes, yes it does.
>
> 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!
hardy: