Database Normalization/Design Question
Jul 23, 2005
Normalization Question - Please bear with me, hopefully things will be
clear at the end of the question.
Given a treaty table containg treaties; Treaty1, Treaty2 etc and a
benefit table; Benefit1, Benefit2 etc. A treaty can only have certain
benefits:-
For example Treaty 1 can process Benefit1 and Benefit2.
To maintain this relationship a new table TreatyBenefit has been
created: -
Treaty1Benefit1
Treaty1Benefit2
A further table called policy has been added. A treaty can have many
policies
Treaty1Policy1
Treaty1 Policy2
A Policy can contain 1 or more benefits
Policy1 Benefit1
Policy1 Benefit2 etc.
Giving structure as follows:-
T - TB - B
|
P
Given the above, should there be a constraint between policy and
TreatyBenefit or Policy and Benefit to enforce referential integrity.
If so which constraint, if not what form of constraint / checking
should I be using, to ensure that a Policy will contain the correct
benefits.
Thanks
Adrian
View 7 Replies
ADVERTISEMENT
Jan 11, 2008
I have a form that I am building and I've run into this problem. There is a section on the form that checks if a person wants to sign up for a AM shift or PM shift for a Show. What I need to know is how do I normalize the form to reflect that in the database? Here is what the user would see on the web form.
Show AM Shift PM Shift
--------------------------------------------------
Show #1 checkbox checkbox
Show #2 checkbox checkbox
Show #3 checkbox checkbox
Here is what I have so far in the table design:
Table Shows
--------------------
ShowID int PK
Name nvarchar(50) Not Null
Table Person
--------------------
PersonID int PK
FirstName nvarchar(50) Not Null
LastName nvarchar(50) Not Null
Table Shift
----------------
ShiftID int PK
Name bit Not Null
View 5 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
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
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
Sep 29, 2006
Hi,
I'm designing a database to represent items in a restaurant menu. An item has the following information associated with it.
Item-
Name, Price, Catagory, Ingredients(many), and Options(many)
Ingredients-
Name, and Price
Catagory-
Name
Options-
Name and Price.
This is how I have it set up so far.
-------------------------------------
Table Item
-ItemID as Primary Key
-Name
-Price
-------------------------------------
Table Ingredients
-IngID as Primary Key
-Name
-ItemID as relationship to Table Item
-------------------------------------
Table Catagory
-CatID as Primary key
-Name
-Item ID as relationship to Table Item
-------------------------------------
Table Options
-OptionID as Primary key
-Name
-ItemID as relationship to Table Item
-Price
I would like to know if i'm normalizing correctly. Much Thanks.
Dan
View 5 Replies
View Related
Jan 15, 2015
I have decided to use Wufoo online forms so collect evaluation data from my clients. Each form will have different type of data.
For example
Form 1 might have:
Client id, name, user id, comments
Form 2 might have:
Client id, name, user id, address, comments, telephone number
Form 3 might have:
Client id, name, user id, comments, date of birth, email address
Each form can have over 20+ data types which can be all different to other forms.My question is: What is the best way to store all the data into sql server without creating new columns of new data types everytime a new form is created in wufoo.
View 1 Replies
View Related
May 13, 2015
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.
View 2 Replies
View Related
Feb 13, 2002
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!
View 2 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
Oct 29, 2015
I actually work in an organisation and we have to find a solution about the data consistancy in the database. our partners use to send details to the organisation and inserted directly in the database, so we want to create a new database as a buffer database to insert informations from the partners then make an update to the main database. is there a better solution instead of that?
View 6 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
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 24, 2006
Hello everyone,I have a webcontrol that uses database-structures alot, it uses the system tables in SQL to read column information from tables. To ease the load of the SQL server I have a property that stores this information in a cache and everything works fine.I am doing some research to find if there are anyway to get information from the SQL server that the structure from a table has changed.I want to know if a column or table has changed any values, like datatype, name, properties, etc.Any suggestions out there ?!
View 3 Replies
View Related
Jul 23, 2005
I have a system that basically stores a database within a database (I'msure lots have you have done this before in some form or another).At the end of the day, I'm storing the actual data generically in acolumn of type nvarchar(4000), but I want to add support for unlimitedtext. I want to do this in a smart fashion. Right now I am leaningtowards putting 2 nullable Value fields:ValueLong ntext nullableValueShort nvarchar(4000) nullableand dynamically storing the info in one or the other depending on thesize. ASP.NET does this exact very thing in it's Session State model;look at the ASPStateTempSessions table. This table has both aSessionItemShort of type varbinary (7000) and a SessionItemLong of typeImage.My question is, is it better to user varbinary (7000) and Image? I'mthinking maybe I should go down this path, simply because ASP.NET does,but I don't really know why. Does anyone know what would be the benifitof using varbinary and Image datatypes? If it's just to allow saving ofbinary data, then I don't really need that right now (and I don't thinkASP.NET does either). Are there any other reasons?thanks,dave
View 7 Replies
View Related
Aug 16, 2007
Hi All,Can u please suggest me some books for relational database design ordatabase modelling(Knowledgeable yet simple) i.e. from which we couldlearn database relationships(one to many,many to oneetc.....),building ER diagrams,proper usage of ER diagrams in ourdatabase(Primary key foreign key relations),designing smallmodules,relating tables and everything that relates about databasedesign....Coz I think database design is the crucial part of databaseand we must know the design part very first before starting up withdatabases.....Thanks and very grateful to all of you....Vikas
View 3 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
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
May 30, 2008
:eek: =>First ,2nd ,3rd normalization
=>Data Integrity
=>Mappings-
1 1:1 mappings
2 1:2 mappings
3 Many :MAny mappings
=>Establish relationship
1.Simple
2.Complex
3. Multiple
Defination of these all or details with subjects also
pls reply me soon
it is urgent for me
thanks in advance
View 5 Replies
View Related
Jul 23, 2005
We need to store land title information about properties in variousAustralian states, but each state maintains it's own land titleregistry and use different columns (well actually differentcombinations of the same columns). For example:Victoria store:TorrensUnitTorrensVolumeTorrensFolioQueensland store:TorrensCountyTorrensLotTorrensPlanTorrensParishTorrensUnitTorrensVolumeTorrensTitleRefThere are 11 different columns and they are used in 8 differentcombinations depending on the state.Since we need to store information about land in different states I seetwo possible solutions:1. A sparse table containing the 11 columns with a CHECK constraint toenforce the valid combinations.2. A table for each state containing only the columns relevant to thestate with a foreign key relationship to the table containing thecommon columns.I'm not sure if the data type and length is consistent between statesyet (waiting to find this out) but assuming that it is which of theseapproaches 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
View Related
Sep 27, 2007
Hi All,
I am designing database where few of the master tables will reside in different database or in case different server. Scenario is
Server "A" with Database "A" may host the "Accounts" table.
Server "B" with Database "B" may host the "Product" table.
I am designing database "Project" which will hosted in Server "A".
My application requires this master tables [readonly access] as data inserted in my application refers this tables. Also there are reports to be generated which refer this tables.
How do i design my database and sql queries?
I am thinking of approach of having equivalent tables created in my database and writing service which keep tables in my database in sync. This will ensure good perfomance during transaction and reports as they will need to refer this table locally as opposed to different database or different server.
Any thoughts on above approach?? or any better/standard way for such scenarios ?
Thanks in Advance. Your inputs will be of great help.
View 14 Replies
View Related
Apr 12, 2007
Hello:
My client has a db with the following structure:
Online US Searchable Map of the 50 US States. Users search criteria is the following: Query records by selecting state, county, then record. Each County table has 10-20 tables. All databases combined = 500MB and TLogs = 100MB.
How would you re-design a relational DB where users could query data by state-county-record. Currenty the DB's are created by the County of each state which creates hundreds of DB's in SQLServer with no realtionship to each US state. What would be the best design to ensure good performance, data integrity and maintenance? Would you create 1 DB with all 50 states, create 4 DB's and divide by region(N,S,E,W), 50 DB's of each state or leave it as is with each county it's on DB? Any suggestions would be appreciated.
thx
rob
View 7 Replies
View Related
