Design patterns are indispensable to improve the quality and productivity of system under development. Even though there are books available addressing the needs of object oriented programming, but useful information for SQL design patterns are hard to find.
I would appreciate if you could share any of the resources that you may have come across.
I am looking for something similar to patterns for table design, but I have been unable to find anything on that, or good rule of thumb performance guides for table design.
Most of the situations we face look like perfect candidates for patterns, and some good rules of thumb -- problems like scalable OLAP requirements PLUS real time reporting.
I am looking for several differnet approaches and a good summary of the trade-offs for each one.
Thanks!
Hi,
I am trying to design some functionality that emulates the recurrence functionality of MS Outlook calendar. In the calendar, users can choose a recurrence pattern (i.e. daily, weekly, monthly or yearly). Depending on the radio button that is selected, different choices appear. I am unsure as to how to model this in the database.
Here's what I have so far:
A ScheduleDetails table that will store information applicable to all schedules, regardless of recurrence pattern. One of the columns in this table will store the recurrence pattern type (0 for daily, 1 for weekly, etc.). The valid values for this column will be stored in a look-up table.
Now, if daily is selected, the user can select a radio button called 'Every <#> days' OR a radio button called 'Every weekday'. My plan is to have a new ScheduleDailyRecurrences table that has a Days field. If 'Every <#> days' is selected, the value of Days will be the value entered by the user. Otherwise, if the user has selected 'Every weekday', the value will be -1. Is this a good way to do it? I feel as though I may be attaching too much meaning to the particular value.
If monthly is selected, it gets even more complicated. The user can either select 'Day <#> of every <#> month(s)' OR 'The <count> <day> of every <#> month(s)'. I'm not sure how to model this. In my ScheduleMonthlyRecurrences table, should I have a field called 'Pattern'? Depending on the value of this field, the details would be then be stored in yet another table? Or should I just have columns in ScheduleMonthlyRecurrences for each user-entered value? This would mean that some of the fields would not be applicable depending on the radio button that the user has selected.
It's a little difficult to explain, but if you go to the Recurrence button in Outlook calendar, you'll see what I mean.
Thanks for any advice.
I would like to fail a package depending on the error. The package extracts data from Excel files. I would like to continue processing if an Excel file is badly formatted, but stop processing if there is a serious issue. like the file server hosting the Excel files crashed.
I was thinking about dynamically changing the MaxeErrorCount property based on the Error ID or description.
Any ideas on an intelligent/simple way to do this
hi all, what sql data type should i use to store variable length bit patterns no longer than 30 bits?
View 4 Replies View Relatedstring starts with 3 digit same number
111 H,777GGG,9999 H etc
string starts with NULL
string that starts with sequential digits
123g,897 k
string that starts with sequential alphabets
abcmki12, ghimkkk, rst123 5 etc
string only one character (digit or letter)
string only has same character repeated 3 times except for OOO
string only has three characters, 1 digit and two letters or 2 digit one letter
string that has only two characters one digit and one letter
I have a database which contains time series data (historical stock prices) which I have to search for patterns on a day to day basis. But searching this historical data for patterns is very time consuming not only in writing the complex t-sql scripts but also executing them.
Table structure for one min data:
[Date] [Time] [Open] [High], [Low], [Close], [Adjusted_Close], [MA], [DI].....
Tick Data:
[Date] [Time] [Trade]
Most time consuming queries are with lots of inner joins. So for example if I have to compare first few mins data then I have to do inner join like:
With IntervalData AS
(
SELECT [Date], Sum(CASE WHEN 1430 = [Time] THEN [PriceRange] END) AS '1430',
Sum(CASE WHEN 1431 = [Time] THEN [PriceRange] END) AS '1431',
Sum(CASE WHEN 1432 = [Time] THEN [PriceRange] END) AS '1432'
FROM [INDU_1] GROUP BY [Date]
)
SELECT [Date] ,[1430], [1431], [1432], [1431] - [1430] As 'Range' from IntervalData
WHERE ([1430] > 0 AND [1431] < 0 AND [1432] < 0) OR ([1430] < 0 AND [1431] > 0 AND [1430] > 0)
------------------------------------------------------------------------
select ind1.[Time], ind1.PriceRange,ind2.[Time], ind2.PriceRange from INDU_1 ind1
INNER JOIN INDU_1 ind2 ON ind1.[Time] = ind2.[Time] - 1 AND ind1.[Date] = ind2.[Date]
where (ind1.[Time] = 2058) AND ((ind1.PriceRange > 0 AND ind2.PriceRange >0) OR (ind2.PriceRange < 0 AND ind1.PriceRange < 0))
ORDER BY ind1.[Date] DESC;
Is there anyway I can use Sql 2005 Data mining models to make this searching faster?
---Checks that input only contains numbers
if PatIndex('%[^0-9]%','11') > 0
Begin
Print 'Not all numbers'
End
Else
Begin
Print 'All numbers'
End
---Checks that input only contains letters
if PatIndex('%[^a-z]%','aaaaa') > 0
Begin
Print 'Not all letters'
End
Else
Begin
Print 'All letters'
End
--Checking for mixed input
If PatIndex('%[^0-9][0-9]%','abc') > 0
Begin
Print 'Alpha numeric data'
End
Else
Begin
Print 'Either all numbers or all letters'
End
--Checks that value must start with a letter and a number
If PatIndex('[^0-9][0-9]%','A1anamwar11') > 0
Begin
Print 'Starts with a letter and a number'
End
Else
Begin
Print 'Does not start with a letter and a number'
End
--Checks that value must End with a letter and a number
If PatIndex('%[^0-9][0-9]','A1anamwar11a1') > 0
Begin
Print 'Ends with a letter and a number'
End
Else
Begin
Print 'Does not End with a letter and a number'
End
--Checks that value must Start with a letter and Ends with a number
If PatIndex('[^0-9]%[0-9]','namwar1') > 0
Begin
Print 'Starts with a letter and ends with a number'
End
Else
Begin
Print 'Does not start with a letter and ends with a number'
End
Namwar
<Link removed by georgev>
I have a report with a chart that utilizes both colors and background patterns to distinguish various pieces of data.
The background patterns work great (they are very clear and provide excellent contrast) when viewed in the report viewer or printed straight from the report viewer.
However, when I export to PDF, the background patterns become compressed and small, almost to the point of being too small to distinguish that there is a pattern at all. The result ends up being what appears to be just a different shade of the background color. This is particularly a problem when you then print the PDF on paper, since hatching patterns are similar to how printers create different shades in the first place.
I am somewhat suspicious that this might have to do with resolution settings for PDF exports. If I could lower the resolution, perhaps the patterns would not become so tight and compressed. Below are examples of what I'm describing:
Screenshot of the report viewer:
Screenshot of the PDF:
My application has a log table with a timestamp field which represents the time when the record was inserted.
We would like query the the table and group the results into date units based on the value of the timestamp.
The grouping specification is held in another table but only specified for a single week.
For example
WeekDay Start End
1 06:00:00 14:00:00
2 08:10:00 17:00:00
What this specification means is that when the log table is queried records with a timestamp that fall on a Monday between 06:00:00 and 14:00:00 should be grouped together, on Tuesday the group is records between 08:10:00 and 17:00:00 and so on...
The only way I can think of doing this is to generate a temporary table when the log table is queried that has a unique record for each time period for the span of the query and then attempt to join this table to the log table using the timestamp and then group based on the temporary table unique ID.
I'm not sure how to generate such a temporary table from the specification table so any help would be appreciated.
Thanks in advance.
Hi,
I would appreciate any thoughts/ideas on the following use case for the distributed service broker application we plan to migrate from our existing proprietary tcp based message protocol using database tables for reliability.
There are two ssb services running in separate sql server instances, each on a different server machine. For simplicity, let us assume the ssb endpoint names are SSBA, SSBB. SSBB is the Initiator of the Dialog while SSBA is the Target. Now the requirement is that if the underlying network communication between the two ssb endpoints(SSBA and SSBB) is broken or if the critical service SSBB is down, then processing of any incoming message into SSBA's queue from a third service broker service (say SSBEXPR) running within a SqlExpress instance should be delayed until SSBB is alive and network communication between SSBA and SSBB is established. In our existing implementation (wherein SSBA, SSBB and SSBEXPR are windows services) we use a combination of TCP socket disconnects and Heartbeat messages between SSBA and SSBB to determine the health of network connection and that of the SSBB service.
Now my understanding of how the underlying network connection for a ssb dialog works is that if there is no activity on a dialog for a certain amount of time then the underlying network connection is closed. Is there a way to specify the amount of time to say infinite value or something and thus change this behavior? My other question is how can one query the underlying network connection (i.e. a row from sys.dm_broker_connections) associated with a particular conversation? If none of this is possible, then any other patterns/ideas/approach is welcome.
Thanks,
Hi,
Using the Microsoft Patterns and Practices "Object Builder" (Dependency Injection/Builder library), I wrote an SQL CLR Stored Procedure (using VS 2005 Professional).
All compiles and deploys ok (to SQL Server 2005 Express).
However, at run-time, I get the following error upon a "BuilderContext" object's instantiation: {"Attempted to perform an operation that was forbidden by the CLR host."}
Thoughts on how to get ObjectBuilder working in the SQLCLR?
Thanks!
Andy
(posted below is the sample code...with the run-time exception occuring on the ...BuilderContext cxt = new ... call)
Microsoft.Practices.ObjectBuilder.BuilderStrategyChain chain =
new Microsoft.Practices.ObjectBuilder.BuilderStrategyChain();
chain.Add(new Microsoft.Practices.ObjectBuilder.CreationStrategy());
Microsoft.Practices.ObjectBuilder.Locator locator1 =
new Microsoft.Practices.ObjectBuilder.Locator(null);
// Get error when new'ing BuilderContext: "Attempted to perform an operation that was forbidden by the CLR host."
Microsoft.Practices.ObjectBuilder.BuilderContext cxt =
new Microsoft.Practices.ObjectBuilder.BuilderContext(chain, locator1, null);
Thanks in advanace for taking the time to read this post.
I am using MSSQL 2005 and have created a function that allows me to use regular expressions in my SQL queries.
My question is I have a pattern buried in a field of misc data that I need to pull out just that pattern and discard the rest of the data. Here is the Regular Expression I am using
select field1 from table1
where dbo.RegExMatch (field1,'[a-zA-Z]{4}[0-9]{6}[a-zA-Z]{2,4}')=1
This returns all values in the field that match the expression. What I want to do now is remove all data from the field on the left and right of the expression that does not match the expression. How would I accomplish this without reading through the 200k+ records and writing rules for every exception I run across?
so I could have Gar b/a ge 'THE GOOD DATA' m/or1 ba4d da....ta. All I want to do is return 'THE GOOD DATA'
I have a scenario like below
Product1
Product2 Product3
Product4 Product5
Product1 1
1 0 0
1
Product2 1
1 0 0
1
Product3 0
0 1 1
0
Product4 0
0 1 1
0
Product5 1
1 0 0
1
How to design tables in SQL Server for the above.
Ok, I'm doing a football database for fixtures and stuff. The problem I am having is that in a fixture, there is both a home, and an away team. The tables as a result are something like this:
-------
Fixture
-------
fix_id
fix_date
fix_played
----
Team
----
tem_id
tem_name
-----------
TeamFixture
-----------
fix_id
tem_id
homeorawayteam
goals
It's not exactly like that, but you get the point. The question is, can I do a fixture query which results in one record per fixture, showing both teams details. The first in a hometeam field and the second in an away team field.
Fixture contains the details about the fixture like date and fixture id and has it been played
Team contains team info like team id, name, associated graphic
TeamFixture is the table which links the fixture to it's home and away team.
TeamFixture exists to prevent a many to many type relationship.
Make sense? Sorry if this turns out to be really easy, just can't get my head around it at the mo!
I would like to create a table called product. My objective is to get list of packages available for each product in data grid view column while selecting each product. Each product may have different packages type (eg:- Nos, CTN, OTR etc). Some product may have two packages and some for 3 packages etc. Quantity in each packages also may be differ ( for eg:- for some CTN may contain 12 nos or in other case 8 nos etc). Prices for each packages also will be different that also need to show. How to design the table..
Product name :
Nestle milk |
Rainbow milk
packages :
CTN,OTR, NOs |
CTN, NOs
Price:
50,20,5 |
40,6
(Remarks for your reference):CTN=10nos, OTR=4 nos
| CTN=8 Nos
Hi all.
I'm going to create a big DB that will hold important info
(the usual stuff - Clients, Products, Orders...)
I wonder where should I use the IDENTITY field,
for example - on Orders I will have Order_ID...
(and where does SQL server the numbers of a deleted records)
My fear is that IDENTITY fields will go wrong somehow so I can
loose connections within the tables
(maybe when restoring my DB to some other locations... with DTS... or other issues)
1. When should I use IDENTITY field ?
2. If I do NOT - how can I lock a record when I add a new one
Hi,
I have 7 web forms wizard. after the user edit the fields, I need to keep a history log of the changed data only.
I am thinking of the following structure
log_id, user_id, field_id, old_value, new_value, transaction
Where field_id is a number indicates the changed field.
for example 1> First Name, 2> Last Name etc
Where transaction is ADD, DELETE, EDIT
Also i need to keep history of the details
for example an enterprise has 10 branches.
State, employees
NY, 1000
MD, 500
My log table will look like this
log_id, user_id, field_id, old_value, new_value, transaction
1, 1, 1, , Mick ,John , EDIT
2, 1, 3, ,NY , HI , EDIT
3, 1, 3, ,MD , , DELETE
4, 1, 4, ,500 , , DELETE
5, 1, 3, , ,NV , ADD
6, 1, 4, , ,700 , ADD
Is there a better way to log changes?
Thanks
I'm going to create a hardware/software inventory program to keep track on all the computers at the office. This program should be able to search the database using queries like "all computers with a cpu faster than 500Mhz".
I want some tips on my database design.
One solution is to create columns for each piece of hardware, i.e. cpu, ram, hdd etc etc. Then just run simple SELECT queries against them. The problem is that a computer may have many HDDs/CDs etc, and also other type of equipment may be entered in the database like switches and routers.
Another solution is to specify the valuetype + value in one table having a relation to another table containing the actual machines/routers/switches. The problem here is that I can't do numeric comparisons this way since "11" is less than "2", ("all computers with a cpu faster than 500Mhz").
Any suggestions?
Table A
[ID] [SalesmanEmail] [SalesmanName]
Table B
[ID] [QuoteNo] [SalesmanID]
Table C
[ID] [Product] [QuoteNo]
Program A creates the records for Table C. In the process it reads from Table A and Table B.
Program B reads record from Table C. Program B now needs [SalesmanEmail] field.
The suggestion is to add [SalesmanEmail] to Table C. Is this good or bad design?
hello,
i am working on a project with another guy as well. well, we have a table called lets say student and student_unit where student_unit holds the unit names the student is doing. and i was planning to use the same student_unit table to hold the result of that unit for that student as it is the case of adding one field but this guy who happens to be my boss thinks that result should be separated. but is it a better idea to make a separate table just for that result which is totally dependent on unit and student? that doesn't make any sense to me.
if asked why, he says for performance, modularity and object oriented approach and blah blah and honestly i think he knows nothing. so i would like u guys to tell him if there is really a need to create a separate table for attributes which is totally dependant on the same PK? having five more attributes on the same table hurts than creating a new table?
I think I might have dug myself in a hole here, so could use some advice on database design.
Currently, I have a table that records meeting minutes. The design is:
Code:
minutesid | int | 4 | no nulls
minutescat | varchar | 255 | allow nulls
minutesnotes | text | 16 | allow nulls
What this does is set up a minutes category, such as "Members Present", and the notes for that category, such as "Mrs. Peacock, Mr. Green, Professor Plum, Colonel Mustard, Miss Scarlett". Each individual category has it's own ID.
I need to develop an archive of each of the minutes. So if there are 15 categories and associated notes for the 15 OCT 2007 meeting, how can I design the archive table to include all of those categories and notes into one record for 15 OCT 2007, then for 15 NOV 2007 and so on ...?
Hope that makes sense. Thanks in advance ...
I have a table to store members. For each member, I have flags like manager, verified, etc. How should I be storing the flags? As individual columns in the same table and using bit datatype or creating a new table called MemberStatus and creating 1 row for each flag?
Thanx in advance.
Premal.
Hi,
I need to design a database, but i i just don know if i am doing it correctly.
Basically, i have an equipment which is sending me a lot of gauges readings (high limit, low limit, current level), electrical readings, hydraulic readings, alarms levels such as Fuel level, temperature level, etc etc, about 200 different types of readings, continuously. I may have different types of equipments and although most of the data are the same, some data types and its availability may be different too., depending on the type of equipment.
I want to keep this readings in a database, and this database will be updated constantly.
Should i create all of this different gauges readings in one big single table? If i do so, i only have one long row of data then for a particular equipment.If i put in different table, i can't see how i can create relationship among my tables with such kind of data.
Can you help on the possible ways of putting this equipment real time readings into DB?
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 B
The data that I have is the following:
Car Manufacturer Factory CarCode
---------------- ------- -------
Ford Houston F-Hou
Ford Reno R-Hou
Chevy Houston C-Hou
Chevy Las Vegas C-LV
Honda SLC H-SLC
Ford SanFran F-SF
Chevy Miami C-Mia
I have a database design implemented already, but I'm receiving some disagreement about it from a co-worker. The way I see it is you have a car manufacturer and a factory location that are both unique, so I created a lookup table for each. I then created a branch table for the many-to-many relationship that exists between Manufacturer and Factory, and within that branch table I placed a column for CarCode, because it seems to me that Manufacturer and Location both determine the CarCode.
I also have an autonumber field within the branch table, which I then use for foreign key relationships.
Is there anything wrong with my design here?
Thank you in advance for your help!
I've fought with this issue for a week now and I can't come up with a simple way of doing it.
I have a database, with the following tables:
Members
MainCategories
Categories
Answers
The idea is that Members will log into the site, select the Categories from the MainCategories, fill out some data, which is stored in the Answers table. The problem is, each of the main categories have different fields. So a Main Category might be "Shoes" and the fields would be "Color,Size,Brand", and another Main Category might be "Cars" and the fields "Make,Model,Year,Color,Type,IsNew".
I thought about creating the Answers table with all the possible answers, linking it to the categories and members table, so we can view which members filled out answers for which categories.
Can anyone help me come up with a solution for this?
Please ignore my earlier post...
Please help me in designing the tables...
I used to get flat files which i need to import into a tables..
The flat files data which contains.....
FlatFiledata:
------------
100,1,xx,yy,,20030101
100,2,20030101
100,3,01,20030101
Below are the tables which i design please correct me..
Table1ForRow1
--------------
col Identity,
col1,100
col2,1
col3,xx
col4,yy
col5,,,
col6,20030101
Table1ForRow2
-------------
col Identity,
col1,100
col2,2
col3,20030101
Table1ForRow3
------------
col Identity,
col1,100
col2,3
col3,01
col4,20030101
When i queried then I want the output:
100,1,xx,yy,20030101,2,3,01
Hello all,I am trying to correctly model the relationship between products andversions within my db. The problem I have is how to store the versiondata. I need to collect the following information:1) Major Build Number (int)2) Minor Build Number (int)3) Build Number (int)4) SP number (int)What is the "correct" schema for this? How many tables and whatcolumns?(On a side note if any one has any links to useful examples of DBdesign could they post them?)Thanks,Jose
View 1 Replies View RelatedI inherited a SQL 2005 database from my bro-in law that has tables with names in the spaces and spaces and other characters in the column names. I link to these tables in an MDB file. Access 2003 sees this fine, but Access 2007 returns odbc--call failed errors. Is there anything I can do short of removing the spaces and characters (? %) from all of the table and column names? Is there a switch I can throw that will tell Access 2007 to ignore this and work like a previous version?
Thanks.
Hi,
I have a form where users can choose number of file uploads ( drop down ) and upload images. I need to store the path of images in database.I want to know what is the best way to store multiple file upload paths. As per my knowledge it possible to store paths in one field with delimiter. Is there any other good way to store the paths.
Raj
Hope this is in the right forum....Assume a DB design which has 3 entities Customers, Vendors, and Email-addresses. Customers and Vendors can have zero, one, or many Email-addresses. To normalize the many-to-many, I have an intersecting entity called Email-addresses-usage. I know how to handle the intersecting entity if it were only Customers and Email-addresses, i'm not sure about adding Vendors to the mix.My questions are:1) in sql managment studio, do I have a FK relationship from Customers to Email-addresses-usage and also a FK relationship from Vendors to Email-addresses-usage?2) If zero email addresses are allowed, how to I define a zero possibility from a relationship standpoint - i.e., do I just specify NOT enforing the FK constraint? but then, if email-addresses are present, I'd want the contraint to be enforced - how is this have my cake-and-eat-too accomplished?3) any tips/suggestions on how one might present this to an end user for data entry.Thanks
View 4 Replies View RelatedHello, I am designing my first database with 5 tables for a demo project and am not sure if it works. an example below.2 of the many things I want visitors to the site to do is find a company by the industry sector they belong to,..andwhat sort of service or products they can supply. For instance a Employment agency maybe under professional services Table 1 Customer Customer_ID = primary key,,,, Sector_ID = Foreign keyComapany Name, Address, Phone, Postcode etcTabel 2 Industry SectorsSector_ID = primary key,,,,Customer_ID= foreign key banking, Education,Prof Services, etc Table 3 Trading ActivityTrading_ID = primary key,,,,Sector_ID = Foreign key, Products_ID= FkEmployment Agent, School, Lawyer etcTable 4 ProductsProducts_ID = primary key,,,,Trading_ID = foreign keySupply frozen foods, transport services, sports goods, etc Table 5 Account Account_ID = primary key,,,,Customer_ID = foreign keyAccount Name, Credit Limit, Payment Terms, Open date, Account contact etc One big point of confusion is, can I have the Customer_ID from the principal Customers tablein every table as a foreign key or must the tables be chained together one after the other as such. Advice appreciatedThanks
View 1 Replies View Related