Primary Key, Normalizing?
Feb 24, 2004
I am a beginner, so please bare with me. I get very confused on how to normalize my database.
Firstly: The employees in the company I work for are in various departments and can have more then one title and work in more then one department.
Example: John Smith can work in the engineering department as a detailer and an engineer and at the same time work as a project manager for the management department.
How do I setup this table structure?
Employees Table
Login (PK) | First | Last | Extension.......
---------------------------------------------
jsmith | John | Smith | 280
Department Title Breakdown
Department | Title
--------------------------
Engineering | Detailer
Engineering | Engineer
Management | ProjectManager
Job Description
Login | Title
-------------------------
jsmith | Engineer
jsmith | Detailer
jsmith | ProjectManager
This is important to break this down because for each project the following is saved:
Project Listing
Project | Detailer | Estimator | Sales | Engineer |....... | Location
10001 | jsmith | jdoe | mslick | sjunk | ...... | Las Vegas
Or should the project be broken down as well
Project Listing
Project | Location
10001 | Las Vegas
Project Team
Project | Member | Activity
10001 | jsmith | Engineer
10001 | mstevens | Detailer
Any thoughts on how to normalize this?
Mike B
View 6 Replies
ADVERTISEMENT
Apr 27, 2006
Greetings all,
I have created an SSIS package that takes data from a very large table (301 columns) and puts it in a new database in smaller tables. I am using views to control what data goes to the new tables. I also specified that it drop the destination table and recreate it prior to copying the data. The reason for this is so that old data removed from the larger database will get removed from the normalized databases.
I have 2 things I am trying to figure out..
1. I would like to have the package set a specific row in each new table to be the primary key (this will allow us to use relationships when querying the data).
2. I decided I wanted to sort the data as it copies. I am using the BI Visual Studio for my editing. In the Data Flow view I cannot seem to disconnect the output from the Source block so I can connect it to the Sort block and then feed that to the output block. What am I missing here?
Thanks
View 7 Replies
View Related
Nov 8, 2006
Please i have created some tables Delivary with this columns (DelivaryId,DelivaryNo,QtyRecieved,DelivaryDate,ProductId) and Product with this columns (ProductId,ProductCode,ProductName,ProductPrice) as you can see the product table keeps record of products whlie the delivary table keeps record of stock supplied. I will like to create another table that will keep record of stock sold out (Invoice Table) based on the qty recieved from the delivaries table
Please help
View 6 Replies
View Related
Aug 10, 2005
So I'm creating an administrative back end for a site that's already been created, and whoever made the tables the site uses didn't know much about database design. So I need to normalize this table of Links so it can be easier to have someone make changes and updates to it, but then I need to put all my normalized tables back together to create a View exactly like the old table which the old site can select from. Basically the stipulation is I can't change the code for the old site so I have to make it think it's still selecting from the same table with the same type of parameters. Is it worth doing all this? Or should I just tough it out with this really ugly table?Here's the table: and here's the site that uses this table:http://waahp.byu.edu/links.aspThanks!~Cattrah~
View 3 Replies
View Related
Aug 28, 2006
Hi
Please can someone point me in the direction, i built a very badly designed database consisting of only one huge table when i first started databases, since learning about normalization i have designed and set up a new database which consists of many more tables instead of just the one. My question is where do i start in transfering the data from the old single tabled database to my new multi-tabled database?
I have MS SQL server 2005 managment studio if that helps, but want to transfer around 200,000 rows of data into the new database. Both new and old databases are on the same server.
thanks in advance
View 11 Replies
View Related
Jul 13, 2006
I have this table...CREATE TABLE #Test (ID char(1), Seq int, Ch char(1))INSERT #Test SELECT 'A',1,'A'INSERT #Test SELECT 'A',2,'B'INSERT #Test SELECT 'A',3,'C'INSERT #Test SELECT 'B',1,'D'INSERT #Test SELECT 'B',2,'E'INSERT #Test SELECT 'B',3,'F'INSERT #Test SELECT 'B',4,'G'....and am searching for this query....SELECT ID, Pattern=...?? FROM #Test....??....to give this result, where Pattern is the ordered concatenation ofCh for each ID:ID PatternA ABCB DEFGThanks for any help!Jim
View 2 Replies
View Related
Aug 24, 2006
I re-designed a predecessor's database so that it is more properlynormalized. Now, I must migrate the data from the legacy system intothe new one. The problem is that one of the tables is a CROSSTABTABLE. Yes, the actual table is laid out in a cross-tabular fashion.What is a good approach for moving that data into normalized tables?This is the original table:CREATE TABLE [dbo].[Sensitivities]([Lab ID#] [int] NULL,[Organism name] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[Source] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[BACITRACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[CEPHALOTHIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[CHLORAMPHENICOL] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[CLINDAMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ERYTHROMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[SULFISOXAZOLE] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[NEOMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[OXACILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[PENICILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[TETRACYCLINE] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[TOBRAMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[VANCOMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[TRIMETHOPRIM] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[CIPROFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[AMIKACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[AMPICILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[CARBENICILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[CEFTAZIDIME] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[GENTAMICIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[OFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[POLYMYXIN B] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[MOXIFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[GATIFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[SENSI NOTE] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]
View 5 Replies
View Related
Jun 9, 2006
Hi,
I have a table like this:
Col1 First_Year Last_Year
a 1990 1993
I want this data to be converted to
a 1990
a 1991
a 1992
a 1993
Is there any simple way to do it in SSIS without using Script Component?
Thx
View 3 Replies
View Related
Dec 27, 2003
THE LAYOUT:
I have two tables: "Applicant_T" and "StreetSuffix_T"
The "Applicant_T" table contains fields for the applicant's current address, previous address and employer address. Each address is broken up into parts (i.e., street number, street name, street suffix, etc.). For this discussion, I will focus on the street suffix. For each of the addresses, I have a street suffix field as follows:
[Applicant_T]
CurrSuffix
PrevSuffix
EmpSuffix
The "StreetSuffix_T" table contains the postal service approved street suffix names. There are two fields as follows:
[StreetSuffix_T]
SuffixID <-----this is the primary key
Name
For each of the addresses in the Applicant_T table, I input the SuffixID of the StreetSuffix_T table.
THE PROBLEM:
I have never created a view that would require the primary key of one table to be associated with multiple fields of another table (i.e., SuffixID-->CurrSuffix, SuffixID-->PrevSuffix, SuffixID-->EmpSuffix). I want to create a view of the Applicant_T table that will show the suffix name from the StreetSuffix_T table for each of the suffix fields in the Applicant_T table. How is this done?
View 6 Replies
View Related
Nov 7, 2005
I have a view with patient data. It looks like below
patid date pulmdc pulmstatus endodc endostatus
100 4/1/05 10 Good null null
100 5/1/05 10 Good 12 Poor
I want to create sql which by each patient, by date, by these four fields ,get
patid 4/1/05 pulmdc-10 pulmstatus-good
patid 5/1/05 pulmdc-1 pulmstatus-good
patid 5/1/05 endodc-12 endostatus-poor
View 1 Replies
View Related
Jan 23, 2006
Hi!
I have a table with the following columns:
account_nr, account_totaling_members, account_type
the account_totaling_members column contains a pipe sperated list of accounts in a varchar: "1001|1002|1003"
I need to normalize this so that i get records like:
"10", "1001", "sum"
"10", "1001", "sum"
"10", "1002", "sum"
..and so forth
Does anyone have any idea how to accomplish this?
View 3 Replies
View Related
Jun 21, 2006
I am copying data from one denormalized table to a COUPLE of normalized ones.
I am using multicast, following advices from the forum.
The problem I have is that the two destination tables (A and B) are sharing a foreign key relationship.Filling in A is no problem, but when I want to fill in B, I don't know how to populate its foreign key, since the multicast doesn't know the corresponding primary key in table A.
View 9 Replies
View Related
Aug 20, 2015
CREATE TABLE CATEGORIES(CATEGORYID VARCHAR(10), CATEGORYLIST VARCHAR(200))
INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1000', 'S01:S03, S09:S20, S22:S24')
INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1001', 'S11:S12')
INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1002', 'S30:S32, S34:S35, S60')
INSERT INTO CATEGORIES(CATEGORYID, CATEGORYLIST) VALUES('1003', 'S40')
The CATEGORYLIST strings are composed of value ranges separated by a colon (:) and multiple value ranges separated by a comma.
The results I need are:
CATEGORYID STARTRANGE ENDRANGE
1000 S01 S03
1000 S09 S20
1000 S22 S24
1001 S11 S12
1002 S30 S32
1002 S34 S35
1002 S60 S60
1003 S40 S40
I have tried taking the original data and parsing it out as an XML file. Is there a less cumbersome way to do this in TSQL?
View 1 Replies
View Related
Nov 6, 2007
I'm new to SSIS and have run into a problem I'm hoping someone can help me with.
Basically, I have a flat file that looks something like:
ID,Type,Description,Results
1,Test1,This is a test,5
2,Test1,This is also a 1 test,7
3,Test1,This is also a 1 test,13
4,Test2,This is a second test,14
5,Test2,This is also a second test,18
I'm trying to normalize the data by extracting out individual rows that have the same "Type" column value. So what I want is to extract each unique type and description into a separate table. This would give me two new rows, one for a type of Test1, and one for a type of Test2, with the descriptions. Does this make sense? Then I could relate the individual results to these test types. In my scenario, I don't care which description is used; I just want to take the first description that shows up with the associated "Type."
Does anyone have any idea of how I could go about doing this? I could pull out all unique "Types" from the rows with the Aggregate transformation, but I'm trying to figure out how to get the description that goes along with it.
Thanks,
Brian
View 1 Replies
View Related
May 22, 2015
I have a large data set with 10s of millions of rows of contact information. The data is in CSV format and contains 48 columns of information (First name, MI, last name, 4 part address, 10+ demographic points, etc.) and I'm struggling with how I should design the database and normalize this data, or if I should normalize this data.
My 2 thoughts for design were either:
Break the columns into logical categorical tables (i.e. BasicContactInfo, Demographics, Financials, Interests, etc.) Keep the entire row in one table, and pull out the "Objects" into another table (i.e. ContactInformation, States, ZIPCodes, EmployementStatus, EthnicityCodes, etc.)
The data will be immutable for the most part, and when I get new data, I'll just create a new database and replace the old one.
The reason I like option 1 is because it makes importing easier, since I can just insert the appropriate columns from each row into the appropriate tables. Option number 2 feels like it would be faster to get metrics on the data, like how many contacts live in which states, or what is the total number of unique occupations in the data set. Plus I'll be able to make relationships between the tables, like which state is tied to which zipcode, which city is tied with which county, etc. Importing that data might be more tricky, since I don't think SQL Bulk Copy will allow for inserting into normalized tables like that.
The primary use for this data is to allow our sales force to create custom lists of contact information based on a faceted search page. The sales person would create the filter, and then I will provide them with the resulting data so they can start making business contacts. Search performance needs to be good. Insert, update, and deletes won't happen once the data has been imported.
What should I look for in designing this database? Any good articles on designing tables around wide data sets like my contact information?
View 6 Replies
View Related
Oct 17, 2012
I need to normalise comma separated strings of tags (SQL Server 2008 R2).
E.g. (1, 'abc, DEF, xyzrpt') should become
(1, 'abc')
(1, 'DEF')
(1, 'xyzrpt')
I have written a procedure in T-SQL that can handle this. But it is slow and it would be better if the solution was available as a view, even a slow view would be better.
Most solutions I found go the way round: from (1, 'abc'), (1, 'DEF') and (1, 'xyzrpt'), generate (1, 'abc, DEF, xyzrpt').
If memory serves, it used "FOR XML PATH". But it's been a while and I may be totally wrong.
View 2 Replies
View Related
May 28, 2015
I have a script that resolve's data into xml like this, ex:
<root>
<title>A</title>
<id>1</id>
<nodes>
<node>
<id>2</id>
<title>A.1</title>
</node>
</nodes>
</root>
And works perfectly, but ... how to make sure every item has an element "nodes" ? The case here is for the child leafs obviously. This, because on the client i have to inject this element "nodes" on a json version of this xml, and just wanted to avoid normalizing the structure on the client.
For the root I am using
FOR XML PATH('root'),TYPE; and for the hierarchy that follows
FOR XML RAW ('node'), root('nodes'), ELEMENTS
View 0 Replies
View Related
Aug 21, 2015
I'm presented with a problem where I have a database table which must be migrated via a "custom tool", moving the data into a new table which has special character requirements that didn't exist in the source database. My data resides in an SQL Server 2008R2 instance.
I envision a one-time query which will loop through selected records and replace the offending characters with --, however I'm having trouble understanding how this works.
There are roughly 2500 records which meet the criteria of "contains bad characters", frequently containing multiple separate bad chars, and the table contains roughly 100000 rows.
Special Characters are defined as #%&*:<>?/{}|~ and ..
While the field is called "Filename" it isn't always so, it is a parent/child table where foldernames are also stored.
Example data:
Tablename = Items
ItemID Filename ListID
1 Badfile<2015>.docx 15
2 Goodfile.docx 15
3 MoreBad#.docx 15
4 Dog&Cat#17.pdf 15
5 John's "Special" Folder 16
The examples I'm finding are all oriented around SELECT statements, to change the output of what I see returned, however I'd rather just fix the entire column using an UPDATE. Initial testing using REPLACE fails because I don't always have a single character as the bad thing in a string.
In a better solution, I found an example using a User Defined Function to modify the output of a select, but I cannot use that UDF in an UPDATE.
My alternative is to learn enough C# to modify the "migration tool" to do this in-transit, but I know even less about C# than I do of SQL.
I gather I want to use @@ROWCOUNT to loop through the rows but I really can't put it all together in a cohesive way.
View 3 Replies
View Related
Jan 11, 2007
Uma writes "Hi Dear,
I have A Table , Which Primary key consists of 6 columns.
total Number of Columns in the table are 16. Now i Want to Convert my Composite Primary key into simple primary key.there are already 2200 records in the table and no referential integrity (foriegn key ) exist.
may i convert Composite Primary key into simple primary key in thr table like this.
Thanks,
Uma"
View 1 Replies
View Related
Aug 28, 2002
Hi all,
Can anyone suggest me on Adding primary key to a table which has already a primary key.
Thanks,
Jeyam
View 9 Replies
View Related
Aug 13, 2007
Hi,
I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key.
For example:
id [unique integer auto incremented primary key - not null],
ClientCode [unique index varchar - not null],
name [varchar null],
surname [varchar null]
isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime.
Regards
Mike
View 7 Replies
View Related
Feb 4, 2015
We have a table, which has one clustered index and one non clustered index(primary key). I want to drop the existing clustered index and make the primary key as clustered. Is there any easy way to do that. Will Drop_Existing support on this matter?
View 2 Replies
View Related
Jan 28, 2004
Hi all
I have the following table
CREATE TABLE [dbo].[property_instance] (
[property_instance_id] [int] IDENTITY (1, 1) NOT NULL ,
[application_id] [int] NOT NULL ,
[owner_id] [nvarchar] (100) NOT NULL ,
[property_id] [int] NOT NULL ,
[owner_type_id] [int] NOT NULL ,
[property_value] [ntext] NOT NULL ,
[date_created] [datetime] NOT NULL ,
[date_modified] [datetime] NULL
)
I have created an 'artificial' primary key, property_instance_id. The 'true' primary key is application_id, owner_id, property_id and owner_type_id
In this specific instance
- property_instance_id will never be a foreign key into another table
- queries will generally use application_id, owner_id, property_id and owner_type_id in the WHERE clause when searching for a particular row
- Once inserted, none of the application_id, owner_id, property_id or owner_type_id columns will ever be modified
I generally like to create artificial primary keys whenever the primary key would otherwise consist of more than 2 columns.
What do people think the advantages and disadvantages of each technique are? Do you recommend I go with the existing model, or should I remove the artificial primary key column and just go with a 4 column primary key for this table?
Thanks Matt
View 5 Replies
View Related
Aug 31, 2006
Hello all,I'm taking over a project from another developer and i've run into a bit of a problem. This developer had a bad habit of not using primary keys when designing various databases used by his programs. So now i've got approx 1000 tables all of which do not have primary keys assigned. Does anyone know of a tsql script that i can run that will loop through each table and add a primary key field?Thanks in advance?Richard M.
View 2 Replies
View Related
Aug 16, 2007
I have a Department Table.
Can any one tell me its Primary Key.
I have the order
AutoNumber, D + AutoNumber, Code,
Can you help me regarding this.
Because some people never like to use AutoNumber.
That's why I am confused.
View 3 Replies
View Related
Nov 8, 2007
Hi..
I'm going to build database of university, but I have problem with primaru key,
This is the situation:
there are many faculities and each one has many departments,
each department has many courses,
each course has many sections..
The problem:
I want to make those fields in the same table and make the primary key generate from other fields,
(i.e)
I want the faculity be integer from 4 digit "Example the first faculity start with 1000 the second 2000 and so on" and the the department of each faculity will generate its value from the faculity number+interger number from 3digit "Example the department of the first faculity start with 1100 and the second on will be 1200 and so on "
the same thing will repeate for courses and sections so the sectionsID will be the primary key.
Do you know hoew this idea can be implement by SQL server 2005?
Please help me as soon as possible.
View 13 Replies
View Related
Mar 23, 2005
A column will be Primary Key. Others are B and C. I want A will contain B and C. I mean B data is X, C data is Y, A will be XY. How can i do this? Can i set in MSSQL or need ASP.NET?
View 1 Replies
View Related
Dec 1, 1998
Heya,
I'm trying to setup a Primary Key on a SQL 6.5 database.
Is there a way to do this? When I hit advanced, it asks for me to select a field for the primary key, but it doesnt list fields to selct from, and I cant type it in.
Thanks for your help,
View 3 Replies
View Related
Jul 8, 2004
Hi All,
Using DTS i have imported the data from sybase to MS SQL server and all the data and tables were imported correctly.But the primary keys are not marked why is it like this?
This is not a one time job and this is meant to be for the customers also.I cannot ask the customers to mark the primary keys themselves. Is there a way to get the keys also.While doing DTS I have marked all the options correctly.
Please help.
View 5 Replies
View Related
Sep 23, 2004
I am setting up some tables where I used to have an identity column as the primary key. I changed it so the primary key is not a char field length of 20.
Is there going to be a big performance hit for this? I didn't like the identity field because every time I referenced a table I had to do a join to get the name of object.
EG:
-- Old way
tbProductionLabour
ID (pk)| Descr | fkCostCode
----------------------
1 | REBAR | 1J
tbTemplateLabour
fkTemplateID | fkLabourID | Manpower | Hours
--------------------------------------------
1 | 1 | 1 | 0.15
-- New way
tbProductionLabour
Labour | fkCostCode
---------------------
REBAR | 1J
tbTemplateLabour
fkTemplateID | fkLabour | Manpower | Hours
-------------------------------------------
1 | REBAR | 1 | 0.15
This is a very basic example, but you get the idea of what I am referring to.
Any thoughts?
Mike
View 11 Replies
View Related
Dec 3, 2004
I need to create my own primary key, how do I go about doing that?? In the database I am working in usually has a primary key that looks like this VL0008
the V is for Vendors, thats basically their number. Some of these Vendors need to be licensed and some dont, the ones that are not licensed dont get a number but I am to use that as the Primary/Index key I need to create one for those particual vendors. How can I go about doing that??? I was wanting to make it TL888 something like that.
View 7 Replies
View Related
Oct 17, 2005
i'm having problem to get th primary key from d database....
for your information i'm using java to get the primary key....
this is my code...
rs = stt.executeQuery("sp_columns "+table_db+";");
while(rs.next())
{
out.write(""+rs.getString("COLUMN_NAME"));
out.write(", "+rs.getString("TYPE_NAME"));
out.write(", "+rs.getString("IS_NULLABLE"));
}
rs = stt.executeQuery("sp_foreignkeys @table_name = N'table_db';");
but the problem is....
i get this error message...could anyone tell me what's the problem....
java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not
find server 'table_db' in sysservers. Execute sp_addlinkedserver to add th
e server to sysservers.
how do i solve this problem....
thanx to anyone who can help me...... :D
View 7 Replies
View Related
Oct 18, 2006
Please help:
I am creating a table called Bonus:
ProductHeading1
ProductHeading2 (could be null)
ProductHeading3(could be null)
Bonus
Datefrom
DateTo
.... what would be the primary key?! I know it would be DateTo and sumfing...... Since Heading2 and Heading3 could be null, they cannot be PK... and heading1 cannot be a PK because the following three DIFFERENT options could have the same heading1
Option 1) heading1 = "X" heading2 = Null heading3 = Null
Option 2) heading1 = "X" heading2 = "Y" heading3 = Null
Option 3) heading1 = "X" heading2 = "Y" heading3 = "Z"
... but I need a PK to make sure a bonus is not entered twice... I considered added an Id, but them how do I assign a id?! what would i make the id equal to???
Thanks....
View 14 Replies
View Related
