Showing posts with label design. Show all posts
Showing posts with label design. Show all posts

Friday, March 30, 2012

ID Column has backed down !

hi
honestly, i can't understand
why the pointer has backed down...
in design view, i see the value 147.720
while the last id-key is 147.772
what happens ?
thanks
atte, Hernn
You need to post your DDL so we can better understand the question. Are you
working with the identity property or is this a home-grown id?
--Brian
(Please reply to the newsgroups only.)
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:essjRIIuFHA.204@.TK2MSFTNGP10.phx.gbl...
> hi
> honestly, i can't understand
> why the pointer has backed down...
> in design view, i see the value 147.720
> while the last id-key is 147.772
> what happens ?
> thanks
> --
> atte, Hernn
>

ID Column has backed down !

hi
honestly, i can't understand
why the pointer has backed down...
in design view, i see the value 147.720
while the last id-key is 147.772
what happens ?
thanks
atte, HernnYou need to post your DDL so we can better understand the question. Are you
working with the identity property or is this a home-grown id?
--Brian
(Please reply to the newsgroups only.)
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:essjRIIuFHA.204@.TK2MSFTNGP10.phx.gbl...
> hi
> honestly, i can't understand
> why the pointer has backed down...
> in design view, i see the value 147.720
> while the last id-key is 147.772
> what happens ?
> thanks
> --
> atte, Hernn
>

ID Column has backed down !

hi
honestly, i can't understand
why the pointer has backed down...
in design view, i see the value 147.720
while the last id-key is 147.772
what happens ?
thanks
--
atte, HernánYou need to post your DDL so we can better understand the question. Are you
working with the identity property or is this a home-grown id?
--
--Brian
(Please reply to the newsgroups only.)
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:essjRIIuFHA.204@.TK2MSFTNGP10.phx.gbl...
> hi
> honestly, i can't understand
> why the pointer has backed down...
> in design view, i see the value 147.720
> while the last id-key is 147.772
> what happens ?
> thanks
> --
> atte, Hernán
>

Monday, March 12, 2012

I really need a debate! Type - Attributes vs Super Type - Sub Types

I have extensively revied both of the design methodologies and I cannot come up with a single clear reason to use one over the other!

Type - Attributes is where you have a table holding the type categories, type, a table holding the type attributes expected and then a table holding the type attribute value:

tbAutombbileCategories
CategoryID | Category
----------
1 | Car
2 | Truck
3 | Motorcycle

tbAutomobileAttributes
AttributeID | fkCategoryID | Attribute
--------------
1 | 1 (car) | Doors
2 | 2 (truck) | Cab
3 | 2 (truck) | Capacity

tbAutomobile
VIN | Category | Make | Model
------------
1 | 1 | Honda | Accord
2 | 2 | Ford | F150

tbAutomobileAttributeValues
fkVIN | fkAttributeID | Value
----------
1 | 1 | 2
2 | 1 | 0
2 | 2 | 1000

Now the above sure is flexible in the sence that a type of automobile can be added without affecting the database schema, but was if some attributes do not take a numeric value? How do you handle computations on attributes specific attributes? Why would I use this structure as opposed to the super type - sub type as shown below?

tbCategories
CategoryID | Category
--------
1 | Cars
2 | Trucks

tbAutomobile (Super Type)
VIN | fkCategoryID | Make | Model
------------
1 | 1 |Honda | Accord

tbCars
fkVIN | Doors |
------
1 | 2

tbTrucks
fkVIN | Cab | Capacity
--------
2 | 0 | 1000

Now, adding new sub types probably isn't very flexible but, now you can specify data types for each attribute instead of using sql_variant, which by the documentation cannot be used in aggregate functions and may render poor result when used with ADO.

Regardless of the method used, alot of back end coding is required for computations, what table to send the attributes, etc...

Can anyone please help me clarify. What method is best and why. So far I am leaning for option 2. More work but seems to be more flexible in the sence of customization of each datatype.

E.G., what if you wanted to specify attributes about the cap that can be supplied to trucks?

tbTrucks
fkVIN | Cab | Capacity | fkCapID
------------
2 | Y | 1000 | 1

tbCaps
CapID | Vendor | Price | et...

Any thoughts at all? I thought this would have been a pretty damn hot topic!

Mike Bhttp://www.databaseanswers.com/normal_forms.htm
http://databases.about.com/cs/specificproducts/g/normalization.htm
http://portal.acm.org/citation.cfm?id=809996&dl=GUIDE&coll=GUIDE
http://databases.about.com/library/weekly/aa091601a.htm

This one is cool

www.bus.tu.ac.th/usr/surat/is304/normal.ppt

This one too...talksing about supertype and subtype

facweb.cs.depaul.edu/yele/Course/IS421/ S6/H10%20ERD%20Advanced%20Concepts.ppt

Used to be Primary Entity and Attributive Entity...whatever|||Originally posted by Brett Kaiser
http://www.databaseanswers.com/normal_forms.htm
http://databases.about.com/cs/specificproducts/g/normalization.htm
http://portal.acm.org/citation.cfm?id=809996&dl=GUIDE&coll=GUIDE
http://databases.about.com/library/weekly/aa091601a.htm

This one is cool

www.bus.tu.ac.th/usr/surat/is304/normal.ppt

This one too...talksing about supertype and subtype

facweb.cs.depaul.edu/yele/Course/IS421/ S6/H10%20ERD%20Advanced%20Concepts.ppt

Used to be Primary Entity and Attributive Entity...whatever
Your last link is invalid.

Anyway, why did you post all the articles on BCNF? Aren't both examples I have illustrated normalized? If they aren't what rules are broken?

I am a newbie, so any explanation would be appreciated!

Also, what method would you use Brett?

Mike B

I never post my problems but I have no time right now

I just released a beta and I am too busy with the testers right now.

New company. Brought into automate the back office and to advise on the design on their software. Lots of sloppiness here. Denormalized tables, inline SQL all of the usual crap I get paid to cleanup.

I have'nt gotten to this part of there process yet but they do some over night processing that can take up to 12 hours. (I can't wait to look at this code).

Data entry people get here at 6 am and their applications can't seem to connect to the SQL Server but the processing has stopped. They just hang and hang. The network guy comes in about 8 am and restarts the SQL Service Fonzi style and everything is fine. The DBA thinks it's a network issue which seems a tid bit ridiculous to me since restarting the service "fixes" it.

I suspecting it's a memory issue.

Any ideas on where to start.since you're one of the good guys i will ask you if you have a preference for what to rename the subject line of your question to, since the current subject is too nebulous|||on the other hand, it is fairly distinctive so i may just leave it

:) :) :)|||rename constipated SQL Server|||Log on to the SQL server, and see what is going on in Task Manager, first. You have probably already done that, but it does sound like a resource problem somewhere (memory, CPU, etc.). Can we hope this is SQL 2000? Or are you stuck with 6.5?

If there are no resource problems, and the operating system is running smoothly, you probably have some sort of open transaction hanging everyone up. The data entry application may have commandtimeout set to "0", which sounds about in line with what you describe of their code.|||My bet is on SQL object locking. I'd check sp_who (definitely not sp_who2 until I knew it was safe) before rebooting to see if a spid is blocking the rest of the known universe.

The lovely little nightly process might be camping on some resource that everybody needs to "wake up the daemons" in the morning. When you kick the ground out from under it (by restarting SQL), the locks are gone and life is lovely once again.

-PatP|||Yes it is SQL 2K.

Thanks guys. I will let you know the resolution. Of course I will have to come in at 6 am. No VPN here. Small shop.

Have a good weekend.|||How long does the "fix" take after it's restarted?

Are the files set to autogrow?

Is the log being blown out?

How do you know if the 12 hour batch windows actually completes.

Is there enough space left on the drives?

Are there any other services on the box that are in competition for the memory?

If there are, my first reccomendation would be to isolate|||Thanks. I will know more on Monday. I just have'nt had much time to think about this due to the revamp project from hell. I plan on being here early to gauge what is going on.

===========================================
How long does the "fix" take after it's restarted?

Immediate

Are the files set to autogrow?

Yes

Is the log being blown out?

Not sure I would have to check the log size before and after.

How do you know if the 12 hour batch windows actually completes.

They tell me so when I ask.

Is there enough space left on the drives?

Yes

Are there any other services on the box that are in competition for the memory?

This box is dedicated the SQL Server.

If there are, my first reccomendation would be to isolate|||My bet: uncommitted transactions in the nightly process.

Wednesday, March 7, 2012

I need some design help (time series analysis)

Hi,
Here is a fictious example that models my real life problem that I'm trying
to solve.
Lets say we have a bunch of baseball hitters and every day we give them an
hour to hit as many 'out of the park' home runs as they can hit. Now we want
to track home many they hit each day and watch whether their totals are
going up or down over time.
My fictious table design (as I see it) would be (with data)
PlayerId TotalHits Date
1 8 1/1/6
2 6 1/1/6
3 10 1/1/6
4 4 1/1/6
1 9 1/2/6
2 8 1/2/6
3 15 1/2/6
4 1 1/2/6
...
This may be poor design, please tell me if it is.
Anyway, from this design I could do a 'select playerid, sum(totalhits) from
xxx group by playerId' to see who has the most hits and look at each of the
top X hitters and see if they're in a incremental pattern or decreasing
pattern or just steady but high hits count type pattern.
Instead of that manual method I would like to figure a way to have the
system automatically tell me whose hit count is increasing over time and
whose are decreasing. and be able to break it down by week. month
quarter...
I'm guessing this might be analysis services in sql server, I've never used
them so I don't know. I run sql server 2000 btw.
If you have articles or references please point me in the right direction.
thanksHi,
the design looks ok, simple and straight forward, if you don=B4t want to
store more information here, you should be fine with that. Sure AS
could help you in some cases, but this should be just a simple query
here. Lets try to find a suggestion which will fit your needs. What do
you want to see at the end of the day, a list with playids only ? The
sums of their hits ? The difference between nother time period ? Which
time period do you want to investigate ? Try to give some sample
resultset that we can suggest something as an query for you.
HTH, Jens Suessmeyer
--
http://www.sqlserver2005.de
--|||"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1151300420.928006.36970@.m73g2000cwd.googlegroups.com...
Hi,
the design looks ok, simple and straight forward, if you don´t want to
store more information here, you should be fine with that. Sure AS
could help you in some cases, but this should be just a simple query
here. Lets try to find a suggestion which will fit your needs. What do
you want to see at the end of the day, a list with playids only ? The
sums of their hits ? The difference between nother time period ? Which
time period do you want to investigate ? Try to give some sample
resultset that we can suggest something as an query for you.
HTH, Jens Suessmeyer
--
http://www.sqlserver2005.de
--
Ultimately I would like to see which players hit counts are on the rise and
which are on the decline. And yes I would like to be able to adjust the time
frames to analyze. So to start out I would like to see the query which would
show me the player id's whose hit counts are on the rise over the entire
time period stored in the database.
I can sort of see how the query could group by week (or month) but I don't
see how it can show me counts which are on the rise.
Thanks for your help.

Friday, February 24, 2012

I need some design help (time series analysis)

Hi,
the design looks ok, simple and straight forward, if you don=B4t want to
store more information here, you should be fine with that. Sure AS
could help you in some cases, but this should be just a simple query
here. Lets try to find a suggestion which will fit your needs. What do
you want to see at the end of the day, a list with playids only ? The
sums of their hits ? The difference between nother time period ? Which
time period do you want to investigate ? Try to give some sample
resultset that we can suggest something as an query for you.
HTH, Jens Suessmeyer
http://www.sqlserver2005.de
--Hi,
Here is a fictious example that models my real life problem that I'm trying
to solve.
Lets say we have a bunch of baseball hitters and every day we give them an
hour to hit as many 'out of the park' home runs as they can hit. Now we want
to track home many they hit each day and watch whether their totals are
going up or down over time.
My fictious table design (as I see it) would be (with data)
PlayerId TotalHits Date
1 8 1/1/6
2 6 1/1/6
3 10 1/1/6
4 4 1/1/6
1 9 1/2/6
2 8 1/2/6
3 15 1/2/6
4 1 1/2/6
...
This may be poor design, please tell me if it is.
Anyway, from this design I could do a 'select playerid, sum(totalhits) from
xxx group by playerId' to see who has the most hits and look at each of the
top X hitters and see if they're in a incremental pattern or decreasing
pattern or just steady but high hits count type pattern.
Instead of that manual method I would like to figure a way to have the
system automatically tell me whose hit count is increasing over time and
whose are decreasing. and be able to break it down by week. month
quarter...
I'm guessing this might be analysis services in sql server, I've never used
them so I don't know. I run sql server 2000 btw.
If you have articles or references please point me in the right direction.
thanks|||Hi,
the design looks ok, simple and straight forward, if you don=B4t want to
store more information here, you should be fine with that. Sure AS
could help you in some cases, but this should be just a simple query
here. Lets try to find a suggestion which will fit your needs. What do
you want to see at the end of the day, a list with playids only ? The
sums of their hits ? The difference between nother time period ? Which
time period do you want to investigate ? Try to give some sample
resultset that we can suggest something as an query for you.
HTH, Jens Suessmeyer
http://www.sqlserver2005.de
--|||"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1151300420.928006.36970@.m73g2000cwd.googlegroups.com...
Hi,
the design looks ok, simple and straight forward, if you dont want to
store more information here, you should be fine with that. Sure AS
could help you in some cases, but this should be just a simple query
here. Lets try to find a suggestion which will fit your needs. What do
you want to see at the end of the day, a list with playids only ? The
sums of their hits ? The difference between nother time period ? Which
time period do you want to investigate ? Try to give some sample
resultset that we can suggest something as an query for you.
HTH, Jens Suessmeyer
http://www.sqlserver2005.de
--
Ultimately I would like to see which players hit counts are on the rise and
which are on the decline. And yes I would like to be able to adjust the time
frames to analyze. So to start out I would like to see the query which would
show me the player id's whose hit counts are on the rise over the entire
time period stored in the database.
I can sort of see how the query could group by week (or month) but I don't
see how it can show me counts which are on the rise.
Thanks for your help.|||"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1151300420.928006.36970@.m73g2000cwd.googlegroups.com...
Hi,
the design looks ok, simple and straight forward, if you dont want to
store more information here, you should be fine with that. Sure AS
could help you in some cases, but this should be just a simple query
here. Lets try to find a suggestion which will fit your needs. What do
you want to see at the end of the day, a list with playids only ? The
sums of their hits ? The difference between nother time period ? Which
time period do you want to investigate ? Try to give some sample
resultset that we can suggest something as an query for you.
HTH, Jens Suessmeyer
http://www.sqlserver2005.de
--
Ultimately I would like to see which players hit counts are on the rise and
which are on the decline. And yes I would like to be able to adjust the time
frames to analyze. So to start out I would like to see the query which would
show me the player id's whose hit counts are on the rise over the entire
time period stored in the database.
I can sort of see how the query could group by week (or month) but I don't
see how it can show me counts which are on the rise.
Thanks for your help.

Sunday, February 19, 2012

I need help in design?

I am trying to create a database that is used to create/store estimates for a manufacturing company. There are many things to consider in this estimate but I will isolate this question to the takeoff itself:

There are several categories in which costs are estimated and they are as follows : Product(s), Site Work, Transportation (Shipping), Field Materials, etc...

Should each of the above have their own "Takeoff" table, or would using one table and a gategory table be a better way to go?

Mike BAre these all products? If so, I would use one table with a category column, probably need more info to give better advice.|||Originally posted by rhigdon
Are these all products? If so, I would use one table with a category column, probably need more info to give better advice.

No they are not all products but, the take off list has the following items

Item | Description | Quantity | Cost | Sub-Total | %adj. | &adj. | Total

So, if I create identity columns on the source tables with the use of categories to create a surrogate key...?

tbProduct
Entry (FK) | ProductID (PK) | Account | etc...
----------------
1 | DT101A | 2301 | ....

tbTransportation
Entry (PK)| Company | Rate
----------
1 | 1 | 450

tbCategories
Category | Description
--------
1 | Product
2 | Transportation

tbTakeoff
Estimate (PK) | Entry (fk) | Category (fk) | Quantity | Cost | ......
------------------------
1000 | 1 | 1 | 1250 | 11.25 |.....
1000 | 1 | 2 | 2 | 450 |...

What do ya think?

Mike B