Best practices: changing values

What is the best way to design your tables in cases where field values change?

Example:
CREATE TABLE Product (ProductID INT, Description VARCHAR(32), Price SMALLMONEY...);

CREATE TABLE Purchase (PurchaseID INT, ProductID INT, Quantity INT);

Since price obviously change over time, I was wondering what the is the best table schema to use to reflect these changes, while still remembering previous price values (like for generating reports on previous sales...)

is it better to include a "Price SMALLMONEY" field in the purchases table (which kind of de-normalizes it) or is it better to have a separate ProductPrice table that keeps track of changing prices like so:

CREATE TABLE ProductPrice (ProductID INT, Price SMALLMONEY, CreationDate DATETIME...);

and have the Purchase table reference the ProductPrice table instead of the products table?

I have used both methods in the past, but I was wanted to get other peoples' take on it.

ThanksBecause price can change for many reasons, I always keep it in the actual transaction row. For example, you might have different prices for a given product based on quantity purchased (for example buying 100 units gets a price break). There might be reasons for different prices based on the customer (one price for wholesale, one for sub-contractors, another price for retail). These differences could be either discreet or cumulative. In short, the price in the inventory table might only be a starting point, the price in the transaction table is the authoritive price for a transaction.

-PatP|||If you want to be able to track historical prices, such as how much a price has changed over time, then you need to add a time dimension to your price table.
But for a financial application such as this there is no substitute to storing the actual price paid in the transation table.|||(which kind of de-normalizes it)

No, it doesn't. :) It's an attribute of the purchase.

The purchase table should have the price paid at time of purchase.

There should be a ProductPrice table that holds the price historically for each price. If you want to avoid duplicating data, you can put the ProductPriceID in the Purchase table so you have the exact price at the time purchase was made.

Best practices for field sizes

Can any one direct me to sources for best practices of field types and sizes
to use for commonly used information such as address, names, city, business
names ...

Thanks, BrianThe maximums you need actually vary depending on country, industry sector,
etc. There's no perfect number ofr any field, so you'll ahve to figure it out
on a case-by-case basis.

Most databases including Access don't usually use up space for all the
characters in a string, just the ones that are actually used. Because of
this, the only reason for ever using a field size smaller than the maximum of
255 is as a data entry sanity check or to discourage people form adding extra
information to the field that actually belongs elsewhere. Sometimes, it's
better to do that check in the application forms, and warn the user without
preventing them from entering the longer value if they insist it's right.

You can usually get a good idea how big a field should be by taking the
biggest value you can find, and multiplying by about 1.66.

On Sun, 27 Feb 2005 20:17:10 -0600, "Brian" <brianh@.advantexmail.net> wrote:

>Can any one direct me to sources for best practices of field types and sizes
>to use for commonly used information such as address, names, city, business
>names ...
>Thanks, Brian|||"Brian" <brianh@.advantexmail.net> wrote in message
news:1124vsnj59p5q9a@.corp.supernews.com...
> Can any one direct me to sources for best practices of field types and
> sizes
> to use for commonly used information such as address, names, city,
> business
> names ...
> Thanks, Brian

If you intend to use the addresses for mailings - I would refer to the USPS
guidelines and standards, and work backwards to set your address fields.

As for names, I try and keep them fairly short (40 chars) and seperate them
into parts (Title, First, Middle, Last and Suffix), but thats just me. Even
if you have big fields, it won't all get displayed in a window envelope.|||Go to your industry standards.

For example, the USPS uses 35 characrter per address line to fit into a
5-line 3.5 inch mailing label.

Best practices - currency elements

Hello! I'm a long-time SQLServer developer, but new to XML.

I find myself doing some XML related to EDI messages.

When you have a field containing a dollar amount, what format should you use in the XSD?

We've been using decimal.

But that's just half the question!

When the XML comes in and there is a round dollar amount, we've been getting the data in integer format, a five dollar order just looks like <mytotal>5</mytotal>.

Wouldn't it seem like a best practice to make this <mytotal>5.00</mytotal>?

Thanks.

Josh

Could this be a problem with the specification of the database column? For example:

Code Snippet

declare @.testo table(myTotal decimal , dec_9_2 decimal(9,2))
insert into @.testo select 5, 3
--select myTotal from @.testo

select myTotal
from @.testo
for xml path('')

/*
XML_F52E2B61-18A1-11d1-B105-00805F49916B
--
<myTotal>5</myTotal>
*/

select dec_9_2
from @.testo
for xml path('')

/*
XML_F52E2B61-18A1-11d1-B105-00805F49916B
--
<dec_9_2>3.00</dec_9_2>
*/

In the first query the source column is simply defined as a DECIMAL column and is displayed without any fractional "decimal" portion. When this column is converted to XML it displays only the whole number portion because really, the data consists of whole number only.

In the second query the source column is defined as DECIMAL (9, 2) column. This provides for 7 whole number digits and 2 decimal digits. When this column is converted to XML it displays the desired decimal places.

Can you provide the DDL for your source column?

|||

The XML is prepared by an outside source, in fact it comes from an EDI message.

I'm wondering whether - more like just how - to raise it with them as an improvement they should make.

Thanks.

Josh

|||

What I would wander is first, are ANY of these fields coming in with decimals. If none, I would definitely raise the issue if your are supposed to be getting 2-decimal accuracy. They may have an error that they are not aware of.

Also, the advantage of getting the decimals is that it eliminates doubt -- which is exactly what you are expressing. It is probably a good idea just to ask the question so that the doubt is eliminated. Much better to talk now than miss something.

sql

Best Practice guidelines

Hi All,

We have an application requirement for a database supporting field service engineers, which calls for a central SQL Server databse, and laptops with the same database replicated onto SQL Express. I'm resposible for designing the database for this, physical and logical. I've designed and built many a database, but never had to use replication before.

I've read through BOL, and understand how the merge replication process works, and I have no problem designing the database assuming it were to run on a single server.

What I am trying to find are whitepapers, or equivalent, on "best design and implementation practice", and especialy common mistakes to avoid.

I know that the windows programmers responsible for the UI will not completely abstract the database from the code (no matter how desirable that is or how often I tell them!), and I really don't want to find I have to change the physical tables or replication logic after they've coded most of the UI .

Many thanks in advance

Richard R

I would say depending on the features you plan to use, Books Online s your best friend.

Let us know if that doesnt help you much and also let us know what specific feature areas you are looking at.

|||

Hi,

BOL is pretty good at describing the process, but doesn't list any caveats. It may of course be that there aren't any - but that would be unusual!

The main question I supppose is, can I just get on and design the system as if it were stand-alone, then build the replication parts afterwards?

I expect I will need some custom logic for reconcilliation, as there has to be a log for tracking part movements, and it is likely that users will synchronise their laptops in a different order to them physically moving the parts, thus generating gaps in the log that need to be filled, as well as there local copy of the data not reflecting the physical reality when they come to move parts.

Is there a best practice for doing this sort of thing without bothering the users?

Thanks for your help,

Richard

best practice analyser

I want to only scan several databases on a server.
So on the database list field, how shall I enter the
names, separated with comma or something else, cannot find
in the help file.
Thanks
You should use a semicolon-delimited list on the database list field.
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"best practice analyser" <anonymous@.discussions.microsoft.com> wrote in
message news:756401c494e8$44305c00$a601280a@.phx.gbl...
> I want to only scan several databases on a server.
> So on the database list field, how shall I enter the
> names, separated with comma or something else, cannot find
> in the help file.
> Thanks

Best datatype ?

One of my users wants to store 20,000 characters in a field. The worst part is he wants to be able to search on that field and he is expecting it to have atleast 10,000 records.
Please let me know what is the best thing to do in this case.
Thanks.I suspect your only choice is to use a text data type. To handle the searching I would setup full text search. Read up on these in Books Online and post back with questions.|||Thanks Paul.

Best data type for a range

I want to know what the best datatype is for a situation like this e.g.if I have a field "age" and the data has a range i.e. 22-30, 31-50 etc, what is the best data type to use for this scenario.

Similarly if I have a field that whereby you use a range for example 1-2 in one record but in another you get an integer value of 0 for instance, what again would be the best datatype.

Many thanks

Integers.

|||

can integers support certain characters such as hypens (-) etc

|||

right not sure how to implement this - the data can't go as 21-30 as it will subract the two values, how I would i add this as a range

|||

If you want the column to stroe values in the form 21-30 etc then varchar(<some length>). The second case also varchar. If I understood your situation correctly.

Or maybe if you are trying to store for each row a lower limit and a higher limit for age, then how about having two columns lower_age_limit and higer_age_limit each perhaps of the tinyint datatype. Or a seperate table altogether for the the age limit, something like tblAgeLimit (id int identity(1,1), lower_limit tinyint, upper_limit tinyint) and then linking the id to the table you need.

|||

Master81:
can integers support certain characters such as hypens (-) etc

No. Sorry - I didn't realise that was the value you wanted to store. You would have to use a varchar. 

Best approach to sending field names dynamically

Hi,

I have a C# web app that searches my database table using the
following search parameters

Search string, criteria (< =) and the field you want to perform your
search on. My understanding is that stored procedure is the way to go.
What's the best way of doing this using stored procedures. Can I
define a placeholder for the field name?

Ex.
SELECT field1, field2... FROM Table WHERE field1='value1' where field1
and value1 are both sent from code.

If it's not possible then what is the best way to approach this
problem? I see so many searches like that on the internet. I can only
do them with inline SQL and not stored procedure.

Thank you
Maz.(maflatoun@.gmail.com) writes:

Quote:

Originally Posted by

I have a C# web app that searches my database table using the
following search parameters
>
Search string, criteria (< =) and the field you want to perform your
search on. My understanding is that stored procedure is the way to go.
What's the best way of doing this using stored procedures. Can I
define a placeholder for the field name?
>
Ex.
SELECT field1, field2... FROM Table WHERE field1='value1' where field1
and value1 are both sent from code.
>
If it's not possible then what is the best way to approach this
problem? I see so many searches like that on the internet. I can only
do them with inline SQL and not stored procedure.

Yes, these sort of searches are not very easy to do with static SQL.
I have an article on my web site that discusses this topic in detail:
http://www.sommarskog.se/dyn-search.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Beginning Balance Query

Hello all,
I would like a total (beginning balance) field in a query be the total for
the year up to the month based on another field within the query, so that
when the fiscal_date = '5/1/2006' that I would have a field contain totals
from '1/1/2006' thru '4/30/2006'. I would like to do this in one query, I am
trying to populate a table with 6 years of data that would have a beginning
balance field and then todays current amount based off of a Fiscal_Date fiel
d.
My problem is I cannot figure out how to do a sum of prior data based off of
another fields current date. Am I trying to do too much in one query to
populate the table?Lyners wrote:
> Hello all,
> I would like a total (beginning balance) field in a query be the total for
> the year up to the month based on another field within the query, so that
> when the fiscal_date = '5/1/2006' that I would have a field contain totals
> from '1/1/2006' thru '4/30/2006'. I would like to do this in one query, I
am
> trying to populate a table with 6 years of data that would have a beginnin
g
> balance field and then todays current amount based off of a Fiscal_Date fi
eld.
> My problem is I cannot figure out how to do a sum of prior data based off
of
> another fields current date. Am I trying to do too much in one query to
> populate the table?
>
Without DDL, I don't know your actual table/field names, but something
like this should be close:
SELECT
Table2.FiscalDate,
(SELECT SUM(Amount) FROM Table1 WHERE DATEPART(year, DateField) =
DATEPART(year, Table2.FiscalDate) AND DATEPART(month, DateField) <
DATEPART(month, Table2.FiscalDate)) AS FiscalAmount
FROM Table2|||Thank you Tracy,
Yes, I did something close to this. What I ended up doing was create 2
tablkes that were identical, and then used a cross join where one of my
criteria is that a.date < b.date, then in the select statement I added a
sum(a.amount). This way I sum all a.amount that is less than b.date.
This way I have all of the b table fields and the total of the amount from
the a table all together.
Thank you,
Lyners
"Tracy McKibben" wrote:

> Lyners wrote:
> Without DDL, I don't know your actual table/field names, but something
> like this should be close:
> SELECT
> Table2.FiscalDate,
> (SELECT SUM(Amount) FROM Table1 WHERE DATEPART(year, DateField) =
> DATEPART(year, Table2.FiscalDate) AND DATEPART(month, DateField) <
> DATEPART(month, Table2.FiscalDate)) AS FiscalAmount
> FROM Table2
>|||Why would you create a second identical table when a VIEW would most likely
have worked -without the issues of keeping 2 tables syncronized?
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Lyners" <Lyners@.discussions.microsoft.com> wrote in message
news:7FA08953-C425-43CD-A198-3CBC08EA229E@.microsoft.com...
> Thank you Tracy,
> Yes, I did something close to this. What I ended up doing was create 2
> tablkes that were identical, and then used a cross join where one of my
> criteria is that a.date < b.date, then in the select statement I added a
> sum(a.amount). This way I sum all a.amount that is less than b.date.
> This way I have all of the b table fields and the total of the amount from
> the a table all together.
> Thank you,
> Lyners
> "Tracy McKibben" wrote:
>|||Good point Arnie. I actually just created a large query that has 2 identical
select statements. The second select statement I total the amount field base
d
on the cross join where clause. I am in the design phase of this report, so
in the future, I will most likely create a view (faster) of the select query
and just reference that instead of requerying everytime.
Thanks for the performance pointer.
"Arnie Rowland" wrote:

> Why would you create a second identical table when a VIEW would most likel
y
> have worked -without the issues of keeping 2 tables syncronized?
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Lyners" <Lyners@.discussions.microsoft.com> wrote in message
> news:7FA08953-C425-43CD-A198-3CBC08EA229E@.microsoft.com...
>
>

Beginning and End of Month Dates

Basically what I am trying to do is as Crystal reports goes through records I want it to check a date field to see if they are the first day or last day of the month. If the first day of the month falls on a Saturday, (DayofWeek = 7), I want it to decrement the date by 1 and if the last day of month falls on a Sunday, (DayofWeek = 1), I want it to decrement the date by 2.

Any ideas?The way of distinguishing between both months can be done like this:

If Month({DateField}) <> Month({DateField}+1) //Obviously is -1 to check for the 1st day
if DayOfWeek({DateField})=6 then
Do This
else if DayOfWeek({DateField})=7 then
Do That

I cant recall if DayOfWeek was 1-based or 0-based but the concept applies. :)|||I don't understand what you mean by:
"If Month({DateField}) <> Month({DateField}+1)"

What does this formula do?|||This formula only does the next set of steps IF the datefield is the last day of the month.

Ie, if you ran this today (31/5/05) :
"If Month({DateField}) <> Month({DateField}+1)"

If Month(31/5/05) <> Month(1/6/05)

...which is true. Today is the last day of the month.

If you want to know if the date is the first of the month, use -1.

Beginners Question

This is probably straight forward, but let's see:

Say I have a Family which includes an address field table and a related
FamilyMembers table which includes an age field.

I need to output a report on each Family where there is at least one
family member with an age over 16.

Any help gratefully received!Can you specify the details of the stuctures of both the tables?

Regards
Debian

*** Sent via Developersdex http://www.developersdex.com ***|||You need to join those two tables. Then you can filter them on condition(s)
you need. This would be one way of doing it, please specifiy structure and
expected result if this isnt good enough:

select ColumnList -- list of columns you need returned
from
Family f
inner join FamilyMembers fm on f.PK_Family = fm.PK_Family
where
fm.Age > 16

-- PK_Family is Primary Key on Family table

MC

"RichMUK" <support@.insurance.uk.com> wrote in message
news:1119522354.673863.165840@.g49g2000cwa.googlegr oups.com...
> This is probably straight forward, but let's see:
> Say I have a Family which includes an address field table and a related
> FamilyMembers table which includes an age field.
> I need to output a report on each Family where there is at least one
> family member with an age over 16.
> Any help gratefully received!|||Thanks debian & MC.

MCs query works fine but I need to report just one record per family.
In this example the tables are:

Family.FamilyID [id]
Family.FamilyName
Family.Address

and

FamilyMembers.FamilyMemberID [id]
FamilyMembers.FamilyID
FamilyMembers.FirstName
FamilyMembers.Age

SELECT f.FamilyID, f.FamilyName, f.Address
FROM dbo.Family f INNER JOIN
dbo.FamilyMembers fm ON f.FamilyID = fm.FamilyID
WHERE (fm.Age > 16)

MC, the query you suggest repeats the same Family. record by the number
of FamilyMembers in the query.
I hope this makes sense.

Rich|||RichMUK (support@.insurance.uk.com) writes:
> MCs query works fine but I need to report just one record per family.
> In this example the tables are:
> Family.FamilyID [id]
> Family.FamilyName
> Family.Address
> and
> FamilyMembers.FamilyMemberID [id]
> FamilyMembers.FamilyID
> FamilyMembers.FirstName
> FamilyMembers.Age
> SELECT f.FamilyID, f.FamilyName, f.Address
> FROM dbo.Family f INNER JOIN
> dbo.FamilyMembers fm ON f.FamilyID = fm.FamilyID
> WHERE (fm.Age > 16)
> MC, the query you suggest repeats the same Family. record by the number
> of FamilyMembers in the query.

SELECT f.FamilyID, f.FamilyName, f.Address
FROM dbo.Family f
WHERE EXISTS (SELECT *
FROM dbo.FamilyMembers fm
WHERE f.FamilyID = fm.FamilyID
AND fm.Age > 16)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp