Solved

SqlServer database design

Posted on 2009-05-09
7
479 Views
Last Modified: 2012-06-27
hi experts , I am new to SQL SERVER database design. work is assigned for a small project database designing. so, how should i proceed for to achieve this goal from starting to ending.

0
Comment
Question by:Tapan Pattanaik
7 Comments
 
LVL 17

Accepted Solution

by:
dbaSQL earned 300 total points
ID: 24344611
Now that's a big one  --  Optimal, effective database design, end-to-end.  Small project databases or VLDB's (very large databases), there are some techniques that apply to both, always, and then there are practices that will vary based on the intended utilization of the database.  

I'm going to cut/paste this in here, because sometimes these pages aren't available unless you have logins.  It's a nice little post on sqlteam.com regarding database design and modeling fundamentals, by Brent Huscher:  http://www.sqlteam.com/article/database-design-and-modeling-fundamentals
Take a look at this and let me know how else I can help.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
This article covers the basics of database design including normalization, relationships and cardinality. A great tutorial on database design.

Database design and the creation of an entity relationship diagram (also known as an "ERD" or data model) is an important yet sometimes overlooked part of the application development lifecycle. An accurate and up-to-date data model can serve as an important reference tool for DBAs, developers, and other members of a JAD (joint application development) team. The process of creating a data model helps the team uncover additional questions to ask of end users. Effective database design also allows the team to develop applications that perform well from the beginning. By building quality into the project, the team reduces the overall time it takes to complete the project, which in turn reduces project development costs. The central theme behind database design is to "measure twice, cut once".

Effective database designers will keep in mind the principles of normalization while they design a database. Normalization is a database design approach that seeks the following four objectives:

minimization of data redundancy,

minimization of data restructuring,

minimization of I/O by reduction of transaction sizes, and

enforcement of referential integrity.
The following concepts and techniques are important to keep in mind when designing an effective database:
An entity is a logical collection of things that are relevant to your database. The physical counterpart of an entity is a database table. Name your entities in singular form and in ALL CAPS. For example, an entity that contains data about your company's employees would be named EMPLOYEE.


An attribute is a descriptive or quantitative characteristic of an entity. The physical counterpart of an attribute is a database column (or field). Name your attributes in singular form with either Initial Capital Letters or in all lower case. For example, some attribute names for your EMPLOYEE entity might be: EmployeeId (or employee_id) and BirthDate (or birthdate).


A primary key is an attribute (or combination of attributes) that uniquely identify each instance of an entity. A primary key cannot be null and the value assigned to a primary key should not change over time. A primary key also needs to be efficient. For example, a primary key that is associated with an INTEGER datatype will be more efficient than one that is associated with a CHAR datatype. Primary keys should also be non-intelligent; that is, their values should be assigned arbitrarily without any hidden meaning. Sometimes none of the attributes of an entity are sufficient to meet the criteria of an effective primary key. In this case the database designer is best served by creating an "artificial" primary key.


A relationship is a logical link between two entities. A relationship represents a business rule and can be expressed as a verb phrase. Most relationships between entities are of the "one-to-many" type in which one instance of the parent entity relates to many instances of the child entity. For example, the relationship between EMPLOYEE and STORE_LOCATION would be represented as: one STORE_LOCATION (parent entity) employs many EMPLOYEEs (child entity).


The second type of relationship is the "many-to-many" relationship. In a "many-to-many" relationship, many instances of one entity relate to many instances of the other entity. "Many-to-many" relationships need to be resolved in order to avoid data redundancy. "Many-to-many" relationships may be resolved by creating an intermediate entity known as a cross-reference (or XREF) entity. The XREF entity is made up of the primary keys from both of the two original entities. Both of the two original entities become parent entities of the XREF entity. Thus, the "many-to-many" relationship becomes resolved as two "one-to-many" relationships. For example, the "many-to-many" relationship of (many) EMPLOYEEs are assigned (many) TASKs can be resolved by creating a new entity named EMPLOYEE_TASK. This resolves the "many-to-many" relationship by creating two separate "one-to-many" relationships. The two "one-to-many" relationships are EMPLOYEE (parent entity) is assigned EMPLOYEE_TASK (child entity) and TASK (parent entity) is assigned to EMPLOYEE_TASK (child entity).


A "foreign key" exists when the primary key of a parent entity exists in a child entity. A foreign key requires that values must be present in the parent entity before like values may be inserted in the child entity. The concept of maintaining foreign keys is known as "referential integrity".


Relationships between two entities may be classified as being either "identifying" or "non-identifying". Identifying relationships exist when the primary key of the parent entity is included in the primary key of the child entity. On the other hand, a non-identifying relationship exists when the primary key of the parent entity is included in the child entity but not as part of the child entity's primary key. In addition, non-identifying relationships may be further classified as being either "mandatory" or "non-mandatory". A mandatory non-identifying relationship exists when the value in the child table cannot be null. On the other hand, a non-mandatory non-identifying relationship exists when the value in the child table can be null.


Cardinality helps us further understand the nature of the relationship between the child entity and the parent entity. The cardinality of a relationship may be determined by asking the following question: "How many instances of the child entity relate to each instance of the parent entity?". There are four types of cardinality: (1.) One to zero or more (common cardinality), (2.) One to one or more (P cardinality), (3.) One to zero or one (Z cardinality), and (4.) One to exactly N (N cardinality).

In conclusion, effective database design can help the development team reduce overall development time and costs. Undertaking the process of database design and creating a data model helps the team better understand the user's requirements and thus enables them to build a system that is more reflective of the user's requirements and business rules. The act of performing database design is platform-independent so persons who use database systems other than SQL Server should also be able to benefit from these concepts.
>>>>>>>>>>>>>>>>>>>>>>>>>>>


0
 
LVL 22

Assisted Solution

by:dportas
dportas earned 50 total points
ID: 24344658
Design for any DBMS begins with conceptual design. Are you familiar with how to design a relational database? If not, then probably best to get some assistance. Learning on the job may not be a good idea if your requirements are more than trivial. If you want to start learning here are two very good books:


Introduction to Database Systems by Chris Date
http://www.amazon.com/Introduction-Database-Systems-C-J-Date/dp/0321197844

Information Modeling and Relational Databases by Terry Halpin
http://www.amazon.com/Information-Modeling-Relational-Databases-Management/dp/0123735688
0
 
LVL 17

Assisted Solution

by:dbaSQL
dbaSQL earned 300 total points
ID: 24344663
Here's another.  http://www.sql-server-performance.com/tips/database_design_p1.aspx
Both this and the previous assume you are going to design the database yourself, from the ground up.  

There are other ways to get it done, too.  Check out the 14-day free trial of ER/Studio from Embarcadero Tech.  Here's a good walk thru of the tool:
http://www.sql-server-performance.com/software/review/er_studio_p1.aspx
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 31

Assisted Solution

by:James Murrell
James Murrell earned 30 total points
ID: 24344914
I would ask what is the project? this may help us get you what you need
0
 
LVL 4

Assisted Solution

by:zstapic
zstapic earned 30 total points
ID: 24344949
If it is small project, just send me a requirements and perhaps I could send you a sql script or ER model.
0
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 90 total points
ID: 24344980
well above are the generic suggestion, here is one more good article, worth to read. have a look

http://www.sql-server-performance.com/tips/database_design_p1.aspx

More help could be possible if you provide with some details.
0
 
LVL 21

Author Closing Comment

by:Tapan Pattanaik
ID: 31579790
Thanks to all for your comments ,

hi zstapic, i will keep you in touch, send you the requirements very soon.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Using a hta file with MSSQL agent to schedule a script job 4 39
SSIS On fail action 5 38
CDC and AOG on MS SQL 2012 13 24
how to restore or keep sql2000  backups useful... 2 14
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question