Normalization Question Regarding Column Combinations
Jul 23, 2005
We need to store land title information about properties in various
Australian states, but each state maintains it's own land title
registry and use different columns (well actually different
combinations of the same columns). For example:
Victoria store:
TorrensUnit
TorrensVolume
TorrensFolio
Queensland store:
TorrensCounty
TorrensLot
TorrensPlan
TorrensParish
TorrensUnit
TorrensVolume
TorrensTitleRef
There are 11 different columns and they are used in 8 different
combinations depending on the state.
Since we need to store information about land in different states I see
two possible solutions:
1. A sparse table containing the 11 columns with a CHECK constraint to
enforce the valid combinations.
2. A table for each state containing only the columns relevant to the
state with a foreign key relationship to the table containing the
common columns.
I'm not sure if the data type and length is consistent between states
yet (waiting to find this out) but assuming that it is which of these
approaches is going to be the most rigorous? I'm leaning towards (2)
but I don't like the feel of a table per state.
View 4 Replies
ADVERTISEMENT
Jan 14, 2008
Hi,
I have a table with 2 columns (S.No,Name) with rows
1,Raja
2,Ramu
3,Rane
I need to generate all 2 pair combinations of the names(i,e)
(Raja -Ramu)
(Raja -Rane)
(Ramu -Rane)
Can some one give me a generalized query for this problem?
Thanks,
Prakash.P
View 4 Replies
View Related
Oct 12, 2007
I was using the code in this thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1371094&SiteID=1) to create a console application which can build the SSIS package dynamically and run the package.
I'm not clear on what all could be the possible key / value combinations for the SetComponentProperty Method. From the examples I have seen its either SqlCommand or OpenRowSet. But I'm not sure about the "AccessMode" with values either 0 or 2. Is there any reference where I get more information on these?
Code Block
// Set the custom properties of the source.
srcDesignTime.SetComponentProperty("AccessMode", 2);
srcDesignTime.SetComponentProperty("SqlCommand", "Select * from devdb..empl_karun");
Thanks
View 5 Replies
View Related
Jul 31, 2004
I want to start with a table that has 4 records:
-Self
-Supervisor
-Peer
-Direct Rep
And I want to end with a table that has every unique combination of these records (the order being reversed would be considered 'unique' in this context)
-Self , Supervisor
-Supervisor , Self
-Self , Peer
-Peer , Self
-Self , Direct Rep
-Direct Rep , Self
-Peer , Direct Rep
-Direct Rep, Peer
How would I do this in an SQL Query? Thanks for your help!
View 1 Replies
View Related
Jul 9, 2006
The requirement in to write a query which will return top 2 rows (in terms of lst_updt_timestamp column) for every combinations of cust_alias_nm, carrier_cd, acct_nbr columns.
Here I wrote a query which selects top 1 only.
Please help me to write to get the top 2nd along with the top 1st row.
select A.cust_alias_nm_id,
A.carrier_cd_id, A.acct_nbr_id,
sum(A.pd_clm_amt) clm_amt,
sum(A.pd_med_amt) med_amt,
sum(A.pd_exp_amt) exp_amt,
A.lst_updt_timestamp
from bal_load_stg A
group by A.cust_alias_nm_id, A.carrier_cd_id, A.acct_nbr_id, A.lst_updt_timestamp
having A.lst_updt_timestamp
in (
(select max(lst_updt_timestamp) from bal_load_stg B
where
A.cust_alias_nm_id = B.cust_alias_nm_id
and A.carrier_cd_id = B.carrier_cd_id
and A.acct_nbr_id = B.acct_nbr_id))
The output looks like:
cust_alias_nm_id carrier_cd_id acct_nbr_id clm_amt med_amt exp_amt lst_updt_timestamp
---------------- ------------- -------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ------------------------------------------------------
aaa 101 1234567891 500.00 500.00 500.00 2005-07-31 00:00:00.000
aaa 102 1234567891 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 100 1234567890 700.00 700.00 700.00 2005-10-31 00:00:00.000
The desired output should look like:
cust_alias_nm carrier_cd acct_nbr clm_amt med_amt exp_amt lst_updt_timestamp
---------------- ------------- -------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
aaa 100 1234567890 700.00 700.00 700.00 2005-10-31 00:00:00.000
aaa 100 1234567890 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 102 1234567891 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 101 1234567891 500.00 500.00 500.00 2005-07-31 00:00:00.000
aaa 101 1234567891 400.00 400.00 400.00 2005-05-31 00:00:00.000
All rows present in the table are
cust_alias_nm carrier_cd acct_nbr clm_amt med_amt exp_amt lst_updt_timestamp
---------------- ------------- -------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
aaa 100 1234567890 700.00 700.00 700.00 2005-10-31 00:00:00.000
aaa 100 1234567890 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 102 1234567891 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 101 1234567891 500.00 500.00 500.00 2005-07-31 00:00:00.000
aaa 100 1234567890 400.00 400.00 400.00 2005-05-31 00:00:00.000
aaa 101 1234567891 400.00 400.00 400.00 2005-05-31 00:00:00.000
Thanks in advance.
View 3 Replies
View Related
May 7, 2008
I am quite new in sql. I am writing a report which takes data of one same column and summing them according to the type as described in another column("TR_1"."TTYPE"). So far I have succeeded to get the sum of only one type at a time (by putting WHERE "TR_1"."TTYPE" = or not equal the desired type). For example: I want to create two columns, one showing the sum of the budget and the other the some of the actuals: here is my SQL instruction (the column "TR_1"."TTYPE" give the record type):
******************************************************************
SELECT SUM("TR_1"."AmountLCU")*-1 "Budget",rtrim("TR_1"."COSTCENTER") "Cost Centre",rtrim("TR_1"."ACCOUNT") "Account Num",rtrim("TR_1"."DONOR") "Donor Num", "TR_1"."AmountLCU"*-1 "Amount","TR_1"."TTYPE", rtrim("TR_1"."ACTIVITY") "Activity Code" FROM "scalaDB"."dbo"."A_GL0601_PREVIOUS" "TR_1"
WHERE NOT ("TR_1"."TTYPE"='' OR "TR_1"."TTYPE"='a' OR "TR_1"."TTYPE"='c') AND NOT ("TR_1"."COSTCENTER"=N'' OR "TR_1"."COSTCENTER"=N'0000') AND (("TR_1"."ACCOUNT">=N'26' AND "TR_1"."ACCOUNT"<N'7100') OR ("TR_1"."ACCOUNT">N'7100' AND "TR_1"."ACCOUNT"<=N'7999'))
GROUP BY "TR_1"."COSTCENTER","TR_1"."ACCOUNT","TR_1"."DONOR","TR_1"."ACTIVITY","TR_1"."AmountLCU","TR_1"."TTYPE"
**********************************************************************
Note: the report is written in Crystal reports and the database is SQL Server (not sure of the version)
Thanks in advance
I.Shaame
View 9 Replies
View Related
Sep 6, 2013
How would I write a query to produce all combinations for a situation such as the following?
Suppose I wanted to write a sentence, "This is [adjective] [noun]." where [adjective] comes from the Adjective table and [noun] come from the Noun table.
Adjective table looks like e.g.
ID Adj
1 good
2 so so
3 bad
Noun table looks like e.g.
ID Noun
1 apple
2 orange
3 banana
And the result set would look like
This is good apple.
This is so so apple.
This is bad apple.
This is good orange.
This is so so orange.
This is bad orange.
This is good banana.
This is so so banana.
This is bad banana.
I would take a stab at this myself and post even something that doesn't work...
View 1 Replies
View Related
Oct 26, 2007
Let's say I have a table MyTable with two colums, One and Two. If I wanted to return unique combinations of these two fields I can do
select distict One, Two from MyTable
How do I return the count of such unique combinations? The following of course does not work but you get the idea.
select count(distinct One, Two) from MyTable
Thanks.
View 6 Replies
View Related
Mar 29, 2013
how to find all possible combinations of values, for example:
My table includes:
Code:
CREATE TABLE temp1 (item varchar(50), ORDER int);
INSERT INTO temp1 (item, order) VALUES ('apple',1);
INSERT INTO temp1 (item, order) VALUES ('pear',2);
INSERT INTO temp1 (item, order) VALUES ('blueberry',3);
I need the output to be like this:
apple
pear
blueberry
apple, pear
apple, blueberry
pear, blueberry
apple, pear, blueberry
I don't need the reverse of each. For example, I need only 'apple, pear'... I don't need 'pear, apple'.
View 5 Replies
View Related
Jan 11, 2008
I need a function or a routine in sql for making all possible combinations of strings with given letters.
means if I give a string 'ab' the function should return
'a,b,ab,ba' if we give 'abc' it should return 'a,b,c,ab,ac,ba,bc,ca,cb,abc,acb,bac,bca,cab,cba'.
return data can be a single list or the list elements can be printed to screen one by one.
I need to use this routine for inserting bulk sample values for a few tables.
Also have limited time to make one by myself.
Please help.
View 4 Replies
View Related
Mar 24, 2008
Hello all, I have an odd requirement. I have a column with a systemgenerated username that is a 6 character, alphanumeric, field. Theseusernames are randomly generated by code. I need to create a storedprocedure that will return all combinations that are not alreadybeingused. Maybe the result of still trying to wake-up from a longweekend.But, I cannot think of an easy way to do this.Any help would be greatly appreciated.Best regards,rbr
View 4 Replies
View Related
Aug 27, 2015
Below query:
Declare @table table
(
consumerID varchar(10),
customerNumber varchar(10)
)
Insert into @table
Select 1,123
union all
[Code] ...
--Expected output concatenation of consumer numbers all permutation and combinations having same
customernumber
1 2
1 3
1 4
2 3
2 4
3 4
5 6
5 7
6 7
View 4 Replies
View Related
Jul 2, 2007
I need to populate a table which have 10 columns with four values. Each row should be a different combination of these four values and the columns can be null too. In other words how can I get all the different combinations for the 4 values that can be in 10 buckets. The final result column based on these values will be generated manually.
For example , I have for grades (P,F, WP, WF) and I have 8 terms and two exams. 8 terms and two exams can have any of the above four values. Based on these grades and terms and exams I need to generate a table which wil be used to determine the student final status Pass/Fail.
What will be the best way to do this and how is it possible. Is there a T-SQL or C# program for this.
If I need to submit this in another forum please let me know.
View 3 Replies
View Related
May 31, 2006
I have developed an ASP.NET form with 12 different fields that will allow end users the cability to query 3 tables that are relational to one another.
I was curious what is the best way to perform this in a Stored Procedure?
Can I use a UNION -- Not sure if this is the best choice
or account for evey kind of WHERE clause based off IF statements on the data that is passed through the parameters?
View 3 Replies
View Related
Aug 17, 2004
Hi all,
I am still confuse in normalization. (1st, 2nd, 3rd, and more.)
Any body have a detail idea abt that ???
Pls explain with example.
Thxs.
Regards,
Shailesh Patel
View 2 Replies
View Related
Nov 6, 2000
Is it possible to normalize a database using SQL statement? I have a huge duplicated records on a certain fields and need to do some normalization on it. For example, the raw data below
Field_A Field_B Field_C Field_D Field_E
1 0001 00/05/11 14.00 Start
1 0001 00/05/11 15.00
1 0001 00/05/11 16.00 End
1 0002 00/05/11 16.10 Start
1 0002 00/05/11 17.50
1 0002 00/05/11 17.10 End
should be normalized to
Field_A Field_B Field_C Duration(End-Start)
1 0001 00/05/11 16.00-14.00=2.00
1 0002 00/05/11 17.10-16.10=2.00
How can I do it using a SQL Query?
Andy
View 3 Replies
View Related
Jan 15, 2008
Hi all,
Does anyone know of a good resource or tutorial that teaches you the basics of the 3 normal forms?
Muchos Gracias
View 2 Replies
View Related
Mar 6, 2008
I'm working on a normalization for one of my classes and I've been sick and I feel lost now, could one of you please look at my database statements and tell me if/what is wrong with it?
FIRE_REPORT(FireID,Alarms,Address,FiremanID,FiremanName,FiremanPhone,FiremanHomeStation,StationAddress,StationPhone,TruckID,License,TruckHomeStation)
I came up with..
TruckID->License,TruckHome
FireID->Alarms,Address
FiremanID->FiremanName,FiremanPhone,FiremanHomeStaion,StationAddress,StationPhone
Which lead to...
FIRE(FireID,Alarms,Address)
FIREMAN(FiremanID,FiremanName,FiremanPhone,FiremanHomeStation,StationAddress,StationPhone)
TRUCK(TruckID,License,TruckHome)
Referencial Integriety Statements
FireID in FIRE_Report must exist in FireID in FIRE
FiremanID in FIRE_Report must exist in Fireman
TruckID in FIRE_REPORT must exist in TruckID in TRUCK
Please, someone tell me if this is right or not. -cheers
View 3 Replies
View Related
Apr 14, 2005
Hi everyone..
Well i have my tables ready to build the database on to the sql server... My probs is normalization of the tables being used in the database....
Is there any best possible way / [short-cut.. very weird to ask this :) ] using the sql server...?
View 6 Replies
View Related
Jun 13, 2005
I have a web app which is used to do normal insert/update of employee info. Connected to each employee that is entered is some data that is imported from an outside source for each employee. The question I have is currently my database is very normalized and importing data from this outside source will be quite a pain because of this. Is it bad practice to denormalize a specific table if no user will every insert/update it beside DTS?
View 11 Replies
View Related
Sep 22, 2001
What's my question is ? Whether there are any tools available for normalization produced by Database vendors or any third party. If yes, Can u kindly give me clear documentation.
Thanks,
venkat.
venkat_26178@yahoo.com
View 2 Replies
View Related
Sep 22, 2001
whether there are any tools for Normalization? If yes, Can u please send some documentation or some reference where I can get them.
-venkat
venkat_26178@yahoo.com
View 1 Replies
View Related
Feb 25, 2004
Does this show "poor" design? It has been suggested to me to do a "Logical Model" of my data base and that will make it easier to "normalize" the tables. I tried this and come up with the following but I don't know if I am stretching it too thin. One rule of the 2NF is to ensure all tables have a primary key, and as you can see, my tbProjectTeam has a primary key, but that is made up of the entire row. Same goes for the tbDepartmentActivities.
tbEstimatedProjects
Reference (PK) | Name | City | Postal |...
-----------------------------------------------------------
1 | Some Project | Niagra Falls | N8E7J5 | ....
tbAwardedProjects
Project (PK) | Reference
-------------------------
1001 | 1
tbProjectTeam
Project (PK)| Login (PK) | Activity (PK)
-----------------------------------------
1001 | jsmith | Detailer
tbEmployees
Login (PK) | First | Last |......
----------------------------------
jsmith | Jim | Smith |.....
tbDepartmentListing
Login | DeptCode
---------------------
jsmith | ENG
tbDepartments
Code | Department
------------------------
ENG | Engineering
tblDepartmentActivities
Code (PK) | Activity (PK)
----------------------
ENG | Engineering
ENG | Detailer
Am I taking this too far or is the above structure something to be expected by a "good" normalized table structure?
Mike B
View 6 Replies
View Related
Jan 31, 2006
Hi,I am using MS-SQL server to store my database.My problem is that I have around 150+ database files in DBF format.Each database file consists of fields ranging from 2 to 33 in number.Also, there are some fields which have just one entry and rest areNULL.This database will be accessed by a printing software.Please advice as to how I should proceed to normalize this database.Regards,Shwetabh
View 2 Replies
View Related
Jul 20, 2005
I've come up with this issue in several apps now. There are things that, fromone perspective, are all handled the same, so it would be desirable that theyall be handled in the same table with some field as a type specification.From other perspective of foreign key relationships, however, they aredifferent things and can't be stored in the same table.For example, I have a scheme for indicating mappings between dimension recordsat one time period to new dimension records at another time period. I coulduse one set of tables for all mappings since they all work exactly the sameway, but then I can't set up DRI between the mapping tables and the dimensiontables. If I just make separate mapping tables for each dimension table, thenI'm creating 4 new tables per dimension table, all identical with respect towhat fields they contain, what kinds of unique constraints they have, and whatrelationships they have to each other with the sole distinction that they eachmap to the integer-type key of a different dimension table. I would not lookforward to doing maintenance on this schema!Is there any strategy for having the cake and eating it, too?
View 7 Replies
View Related
Feb 13, 2008
I have 3 code tables :
create table cd_fiq_a
( fiq_id int not null primary key, fiq_name varchar(50) not null)
create table cd_sal_b
( sal_id int not null primary key, sal_name varchar(50) not null)
create table cd_rak_c
( rak_id int not null primary key, rak_name varchar(50) not null)
insert into cd_fiq_a values (1, 'Fiq1')
insert into cd_fiq_a values (2, 'Fiq2')
insert into cd_sal_b values (1, 'Sal1')
insert into cd_sal_b values (2, 'Sal2')
insert into cd_sal_b values (3, 'Sal3')
insert into cd_sal_b values (4, 'Sal4')
insert into cd_sal_b values (5, 'Sal5')
insert into cd_rak_c values (1, 'Rak1')
insert into cd_rak_c values (2, 'Rak2')
insert into cd_rak_c values (3, 'Rak3')
insert into cd_rak_c values (4, 'Rak4')
Now there is a relationship between cd_fiq_a, cd_sal_b and cd_rak_c. For a given Faq there can be one or more records of Sal. For a given Fiq and a given Sal there can be one or more records of Rak.
I am thinking that i can do it one table or two tables:
One Table Solution
----------------------------
create table relation_d
( relation_id int not null primary key,
fiq_id int not null foreign key REFERENCES cd_fiq_a (fiq_id),
sal_id int not null foreign key REFERENCES cd_sal_b (sal_id),
rak_id int not null foreign key REFERENCES cd_rak_c (rak_id),
sort_order int not null )
Two Table Solution
---------------------------
create table relation_header_d
( relation_header_id int not null primary key,
fiq_id int not null foreign key REFERENCES cd_fiq_a (fiq_id),
sal_id int not null foreign key REFERENCES cd_sal_b (sal_id) )
create table relation_detail_e
( relation_detail_id int not null primary key,
relation_header_id int not null foreign key REFERENCES relation_header_d (relation_header_id),
rak_id int not null foreign key REFERENCES cd_rak_c (rak_id),
sort_order int not null )
Which solution is more normalized and will result in better execution of Sql? Or is there any other solution which is more better?
Thanks
View 11 Replies
View Related
Jun 8, 2007
Hello all,
My question concerns the amount of normalization i require for my specific needs. I realize this is a difficult question without knowing alot more about my database. I am hoping with some information I could get advice from those more experienced than I.
- My database consists of 9 tables with the maximim number of columns being 11 which is the contacts table.
- the largest data type is nvarchar 125.
- number of rows in the largest table will eventually grow to hundreds of thousands.
- users access the database online
This is large to me but I expect that to some of you this not.
My application would be easier to setup if the contacts table were to include address info.
So my question is, for a database of this size could I create a contacts table similar to the customer table in the Microsoft Northwind sample data base with the address included, or should I model something more like the contact table in the Microsoft Adventureworks db with the address and State/province split to separate tables.
Any help you could provide with this scetchy info would be greatly appreciated.
View 6 Replies
View Related
Oct 18, 2006
Hai everybody recently i came across this article and i have tried to answer all the follwoing questions. But i am not sure its correct or not..so you peoples can comment on the follwoing questions.
2)
Employee (ssn, Name, Salary, Address, ListOfSkills)
Yes,
No. Ans: No. as list of skills would be repeated.
3)
Department (Did, Dname, ssn)
Yes,
No. Ans: No. ssn and did should be moved to a seperate table.
4)
Vehicle (LicensePlate, Brand,
Model, PurchasePrice, Year, OwnerSSN, OwnerName
Yes,
No Ans: No.
5)
Employee (ssn, Name, Salary, did) (obs.:
employee can only belong to one department)
Yes,
No. Ans: Yes.
6)
Customer (Cust_Id, Name, Salesperson, Region) where Salesperson
determines Region.
Yes,
No. Ans: No.Salesperson and region should be moved to a seperate table.
7)
Component (ItemNo, ComponentNo, ItemName, Quantity) where ItemNo
-> ItemName
Yes,
No. Ans: No.As itemname is a subset of itemno and not a subset of both itemno and componentno.
View 3 Replies
View Related
Feb 14, 2008
Is it worth it for performance purposes to create additional fields in table so that when a database query is made two tables don't need to be joined?
View 1 Replies
View Related
May 28, 2008
Hi All
Please guide me in the following situation. I am new in programming
I have a master table tblCompany with fields: Company Name, Address, Phone number
Second table is tblUsers with Company Name, User Name , Password
Third table is tblDealing with field Company Name , Dealer Name, Dealer Address
According to the normalization rules I shoud put a column named Company_Id in tblCompany(master table)
and use it in other two tables instead of CompanyName colum to reduce the data retundancy.
But my question is accessing data from master detail tables with join quries will take more processing time(taking the company name against the company ID). On the other hand memory wise its same to store the company ID(like 0012786) and company name (like somecompany Ltd). So should I go for normalization or simply store the Company name in each table.
Thanks
View 4 Replies
View Related
Dec 8, 2005
Hi all,
This is actually a pretty stupid question, but somehow I need an answer from you experts.
We are currently building a web application using ASP.NET, and it simply manages contact information, like outlook. Contact information include first name, last name, birthday, etc. It also tracks address, phone number, and email. Here come the problem.
We allow only one address, 4 phone numbers and an email for each contact. When we building the database table, should we create 6 fields to contain all the information or should we create address, phone, and email table and then create the relationship between them.
Will the first method speed up the performance? Or the second method is the proper way?
I need some pros and cons on each
Thanks so much for your opinion
Sam
View 5 Replies
View Related
Jul 20, 2005
helloI've a denormalized table PRODUCTS with following fields:ProductNo ,OrderNo ,SerialNo ,OrderDate ,PromiseDate ,ManufacturerID ,......DistributorID ,DealerID ,......ReceiptDate ,......I have to denormalize this table, so I created 3 tables:Table Name : ProductOrdersFields:+ProducrOrderID,ProductNo ,OrderNo ,SerialNo ,OrderDate ,PromiseDate ,ManufacturerID ,-------------------------------Table Name: ProductsOrdersDetailsFields:+ProductsOrdersDetailsID,ProductOrdersID,DistributorID ,DealerID......-----------------------Table Name: ProductsOrdersReceiptsFields:+ProductsOrdersReceiptsID,ProductsOrdersDetailsID,ReceiptDate ,......----------------------------DistributorID and DealerID appear in Details table because aparticular order number for a specific product number can come fromdifferent distributor and dealer.What I need is a query to populate these normalized tables from theoriginal denormalized Products table.Can any one please help?Thanks
View 1 Replies
View Related
Feb 23, 2006
Hi!
I wonder what would be the best (at to be honest - how to do it at all) to perform data normalization with SSIS. The scenario is as follows:
I got plain table with several columns in it.Some of columns can be copied straight into destination tableSome columns (String) should be lookup in another table to get IDOn success just replace string with IDOn fail - create new record in lookup table and return newly created ID
Thanks for any ideas and maybe short samples
Anrijs Vitolins
View 1 Replies
View Related