Table Design For Recurrence Patterns
Oct 17, 2007
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.
View 5 Replies
ADVERTISEMENT
May 9, 2006
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!
View 1 Replies
View Related
Feb 13, 2008
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.
View 21 Replies
View Related
Jan 31, 2008
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
View 1 Replies
View Related
Aug 18, 2015
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
View 3 Replies
View Related
Feb 21, 2007
hi all, what sql data type should i use to store variable length bit patterns no longer than 30 bits?
View 4 Replies
View Related
Jul 6, 2012
string 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
View 1 Replies
View Related
Feb 17, 2008
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?
View 1 Replies
View Related
Sep 5, 2007
---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>
View 13 Replies
View Related
May 16, 2012
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:
View 2 Replies
View Related
Apr 27, 2006
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.
View 5 Replies
View Related
Dec 29, 2006
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,
View 8 Replies
View Related
Feb 6, 2007
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);
View 1 Replies
View Related
May 6, 2015
We need to Insert/Update a Fact Table from staging Table. currently we are using a SP which update Fact Table for Each region. this process is schedule, every 5 min job is run and Update fact table.but time of Insert and Update too long from staging to Fact, currently we are using merge statement for Insert and update.in my sp we are looping number how many region we need to update and at a time single Region we are updating using while loop in current SP.
View 7 Replies
View Related
Oct 7, 2015
I have a requirement of table partitioning. we have 10 years of data on a table which is 30 billion up rows on 2005 server we are upgrading it to 2014. we have to keep 7 years of data. there is no keys on table or date column. since its a huge amount of data and many users its slow down the process speed. we are thinking to do partition on 7 years for Quarterly based. but as i said there is no date column on table we have to use reference table to get date. is there a way i can do the partitioning with out adding date column on table? also does partition will make query faster?
I have think three ways to do it.
1. leave as it is.
2. 7 years partition on one server
3. 3 years partition on server1 and 4 years partition on server2 (for 4 years is snapshot better?)
View 3 Replies
View Related
Feb 29, 2008
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'
View 4 Replies
View Related
Jul 30, 2015
I have to tables like given below Landing table "A" (Data load will happen over here, No primary keys mentioned over here) table "B" .Now I want to move the data from A to B.I have made use of below query insert into B select * from A...Landing table "A" has huge no of records, MS SQL server is taking huge amount of time.any alternative way to make this insertion process faster?
View 6 Replies
View Related
Apr 30, 2008
Hi, I am developing an application to a garment factory. I have a doubt in designing a table.Basic tables:Jobs, JobColors, Material, Units, Currencies ...These tables are designed with normalization rules.I got a problem at PurchaseOrderDetailsMain
table is JobMaterial. It has materialid, jobid, supplierid, description
and TypeFactor(which represents the type of order) means that the
material is ordered based on size or colors or total qty.1 for ByColor, 2 for BySize, 3 for ByQty, 4 for By Contrast colorsThe main problem at the details of the sub table.JobMaterialDetailsIf typefactor is by size, i need to store the details based on sizeex: S - 2000pcs, M - 4000pcs, L - 4000pcs, XL - 2000pcsSo I will have 4 records per each sizeIf it is by color, White - 3000pcs, Portabella - 5000pcs, Black - 2000pcs.If it is by general, Total qty 10000pcsHow
can I design this table. If I take, ColorOrSize column, it will refer
different values for diffrent typefactor. When by size, it will have
Size and when by color, it will refer colorcode.But colors are having referential integrity. So it is violated other than by color as typefactor.What is the best way to design this table?Can anybody suggest?Thanks in advance
View 2 Replies
View Related
Jul 24, 2000
I am desingning a table and i have a column OrderID and another column call Order, is neccessary to use a primary key, because One OrderID may have many Orders?
Thanks.
View 5 Replies
View Related
Aug 21, 2000
Coming from a support background and having to design my first database I have a couple of questions re- table design. Firstly I have set up several tables and included one field (of the same name) in each. This is a primary key in one table with an incremental seed. I would like this info to appear in the other tables although these can be duplicates in the other tables. How is it best to achieve this relationship. From reading it suggest FK in the relationship application but looking at other databases this seems to have been achieved by some other means. Is it more common to use stored procedures to enforce this? If so please add pointers. Secondly, I have set up a couple of master tables to act as looks ups for fields in other tables. Again how do I get this to look up the table - is it done through stored procedures or at the time of writing the front end application?? Sorry if this is all basic stuff but it is new to me.
Thanks for any help
View 1 Replies
View Related
Oct 26, 2004
CREATE TABLE [dbo].[table1] (
[aaa] [bigint] IDENTITY (10000, 1) NOT NULL ,
[bbb] [int] NOT NULL ,
[ccc] [int] NOT NULL ,
[ddd] [bigint] NOT NULL ,
[eee] [int] NOT NULL ,
[ffff] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[gggg] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[hhh] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[iii] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[jjj] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[kkk] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lll] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mmm] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[nnnn] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ooo] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ppp] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[qqqq] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rrrr] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ssss] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tttt] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[uuuuu] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vvvvv] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wwwww] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[xxxxx] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[yyyyy] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zzzzz] [int] NULL ,
[abc] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[def] [datetime] NULL ,
[ghi] [datetime] NOT NULL ,
[jkl] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mno] [bigint] NULL
) ON [PRIMARY]
I have created a table with above column width. The rowsize is more than 8kb.And the table holds millions of rows of data. So is it a correct way of designing the table?
Or how can I redesign this table.
Thanks.
View 1 Replies
View Related
Mar 16, 2004
I'm currently developing a real estate system to manage order processing and work flow. I'm a little uncertain as to how to design the tables because an order can have N number of applicants, owners, buyers, and properties. There are cases where there are 9 different buyers and some where the number of properties exceeds 20. It seems that normalization might make the situation crazy, but I'm a touch rusty. Thanks.
View 2 Replies
View Related
Apr 16, 2004
I am developing an estimating application and at the "quantity takeoff" level, all products/equipment/sub contracting/etc.... end up on the same table because of the common elements:
Item | Quantity | Unit Price | SubTotal | Total
Now, since the unit price of a product is not determined from the same tables as the unit price of equipment rentals or sub contract work, etc...
how do I direct the attention to the appropriate table when trying to obtain the unit price or edit the unit price etc...
If my question is not clear, let me know, I will try to clarify.
Mike B
View 3 Replies
View Related
May 10, 2008
Hi,
Is there a way to copy and save my Design Table? I want to transfer it to another workstation.
View 8 Replies
View Related
Feb 13, 2006
Hi am trying to figure out my table design need some advice
I have a table that catches the telephone numbers of a customer , there are 5 telephones for each customer. All the numbers are there in the DB
Should I create the design in the following format
CustomerName
Tel1
Tel2
Tel3
Tel4
Tel5
Or Should I have
Customer
Telephone
& then have 5 rows for each customer
Please advice
I even wanted to knw how should the design be , if all the Tel Nos are not captured, I mean what if some customers have 3 tel Nos, while some may just have 1 while some may have all 5
Thanks
View 7 Replies
View Related
Aug 21, 2006
Hi I'm having trouble figuring out this database design and normalising the data. Following are the tables and I am wonder if it can be normalised any further. This is a database of Architects and their work history and building they have designed.
Architects:
FirstName
LastName
Gender
DOB
Bio
Works:
Title
Client
Type_of_Building
Style
Address
Year_Designed
Year_Completed
Comments
Architect_ID
Firm: (this is the various firms that the architect has worked for)
CompanyName
Address
EmploymentHistory: (when the architect worked with a particular firm)
Year_Start
Year_End
Firm_ID
Architect_ID
Publication: (where the architects works were published)
Author
Date_Publication
Title
Source
Publisher
Architect_ID
I would really appreciate some help and suggestions on creating the relationships.
thanks
View 5 Replies
View Related
Sep 17, 2007
I create following tables.
employee table
------------------------
EMP_ID varchar
NAME varchar
DEPARTMENT_CODEint
POSITION_CODEint
COUNTRY_CODEint
department table
------------------
DEPARTMENT_CODEint
DEPARTMENT_NAMEvarchar
position table
---------------
POSITION_CODEint
POSITION_NAMEvarchar
country table
-------------
COUNTRY_CODEint
COUNTRY_NAMEvarchar
In employee table I have to store two country names.
ie. employee currently working in this country
but his home country name (origin country) is another
name. In country table all countries information available.
ie. country_code and country_name.
How I can design the employee table that it get country_name from COUNTRY TABLE ?
but we can not use same country_code in two fields.
OR
otherwise I will have to create employee table like this
employee table
------------------------
EMP_ID varchar
NAME varchar
DEPARTMENT_CODEint
POSITION_CODEint
COUNTRY_CODEint
COUNTRY_NAMEvarchar
ie. during data entries direct insert the country_name in employee
table ?
I want to create separate table for separate data.
department,postion,country.
How I can use unique country_code in employee table's two
fields ?
regards
Martin
View 2 Replies
View Related
Nov 8, 2007
Hi,
I'm struggling coming up with an optimal table design for the following scenario.
I have a table in which each row is recording information about a document - author, publisher, publication, title, abstract and so forth. I also have an image table which contains information about the images which can be associated with the documents.
Each document can have on of three image types associated with it - an author photo, a publisher logo and a publication logo. There aren't actually that many different authors, publishers and publications in the database so it would be common for many documents to have the same author or the same publisher although authors sometimes write for different publications. In the current setup it's possible for a document to have more than one image of each type associated with it, which is undesirable - this needs to eliminated and we need to ensure that each document can only have one image of each type associated with it.
It'd also be a huge boon if I could get all the information about a document and it's attached images with a single database call. In the current setup, trying to do this returns multiple rows so I've ended up making two calls, one for the document and one for it's attached images.
I can see two possible ways of setting this up, neither of which appeal.
The first is to add three columns to my document table to record the image URLs which is simple but inflexible should I want to add more image types later on.
The second is to set up three new tables for author, publisher and publication and associate each of these with the document table through a key and then to the image table through another key. There's also an image type table which is going to come in to play. I'm unwilling to do this because it's going to just explode the number of tables I've got, especially if we add more image types, and play havoc with my XML schemas.
Is there another way of rationalizing this that I've missed?
Cheers,
Matt
View 3 Replies
View Related
Jan 11, 2007
Hi,
I am trying to decide between 2 table designs. I basically have several different types of work items that need to be tracked. Each work item has their own set of statuses, some of which may be the same as other work item's statuses, some of which will be unique for that work item.
Now, should I create one status table for each work item, or would it be better to use one status table, but add a column to track which work item the status belongs to?
I'm not sure which is a better design, or perhaps there is some alternate design that is better. Any help is appreciated.
Thx.
View 1 Replies
View Related
Sep 20, 2007
Currently I need to design database table and got stuck.
I have a project and its sub projects. A parent project and its sub projects share the same requirement.The description of the requirement will be changed every day.
For example
Project A - Do Some thing 1 on Project A [09/28/2007]
Project A-A1 - Do Some thing 1 on Project A [09/28/2007] , Do Some thing 2 on Project A [09/29/2007]
Every time the comments updated, both project A and project A-A1 should share the same update i.e select project A and its subproject will be displayed
Project A Do Some thing 1 on Project A [09/28/2007] , Do Some thing 2 on Project A [09/29/2007]
Project A-A1 Do Some thing 1 on Project A [09/28/2007] , Do Some thing 2 on Project A [09/29//2007]
When you tried to insert new sub project, the requirement will be shared with the parent project ie.
Project A - Do Some thing 1 on Project A [09/28/2007]
Project A-A1 - Do Some thing 1 on Project A [09/28/2007] , Do Some thing 2 on Project A [09/29/2007]
Project A-A2 - Do Some thing 1 on Project A [09/28/2007] , Do Some thing 2 on Project A [09/29/2007] , Do Some thing 2 on Project A-A2 [09/30/2007]
assume Project A-A1 and Project A-A2 are sub project of project A
Could anyone show me how to design the database table for those mess?
Thank you
View 6 Replies
View Related
Jan 3, 2008
Hi All,
If I have the following data that I need to put in a lookup table, what would be considered the proper create table schema for it?
G, "Group"
R, "Region"
D, "District"
Would you setup your table like this:
Level_ID Varchar(1),
Level_Desc Varchar(20)
Or would you go ahead and create like this
Level_ID Integer,
Level_Code Varchar(1),
Level_Desc Varchar(20)
Not sure which one would be the preferred method.
Thanks,
JJ
View 5 Replies
View Related
Dec 17, 2007
I have a huge database and off lately, I have been experiencing some performance issues on some queries. Some of my tables now have over 10 million records. I noticed the performance issue was caused mainly from one table created as a work around. If I remove the column from the workaround table (but still leave the table in the query), I get the results in 1 second. However if I include a column from the workaround table, the same query takes 14 seconds (same output only an extra column) . I will appreciate any advise on a better design to get around my workaround table.
Below is my scenario
Table - Team
-------------------
Column -
TeamID (Primary Key)
TeamName
Table - Matches
---------------------------
Columns -
MatchID (Primary Key)
MatchName
Team1ID (Foreign to "TeamID" in Table "Team")
Team2ID (Foreign to "TeamID" in Table "Team")
Table - Players
----------------------
Columns -
PlayerID (Primary)
TeamID (Foreign to "TeamID" in Table "Team")
PlayerName
The above tables work very well... The tables I listed below are my cause of issue... I need to write a query to list every match with the names of players who played in it comma delimited (one row per match).
I created a table
Table - MatchPlayer
-----------------------------
Columns -
MatchPlayerID (Primary)
MatchID (Foreign to "MatchD" in Table "Match")
PlayerID (Foreign to "PlayerD" in Table "Player")
But I couldnt write a query that would list me all the players comma delimited in one row per match..
So I put a workaround
Table - MatchPlayer1
-----------------------------
Columns -
MatchPlayerID (Primary)
MatchID (Foreign to "MatchD" in Table "Match")
Team1PlayerNames (Comma Delimited values - varchar(800))
Team2PlayerNames (Comma Delimited values - varchar(800))
This allows me to easily list the player names comma delimited per match but is a very bad design. Is it possible to list the same using the table Match Player.
Thanks in advance
View 5 Replies
View Related
Jan 3, 2007
hi there,im new to sql server 2005 and this forum
need help here pertaining to the database table definitions
all was well when i started using it a month or 2 ago until now that when i tried to redesign my tables,it wouldnt prompt me to choose a primary key for my table
previously what happened was that when i saved the table without selecting an attribute as a primary key,it would prompt me to do so nu could choose to have the primary key auto-generated
but now it seems it stopped prompting me about it
whats wrong with it?anyone else faced this?
pardon my question,im a noob at this sql server thingie
thanx in advance!
View 12 Replies
View Related
