November 20, 2007 at 6:55 am
I want to know about Database design. If we think about 'DataBase Design', which would trigger first in our mind ?
Pls dont tell 'do a search'. I am expecting some good answers from best experts.
karthik
November 20, 2007 at 8:45 am
First questions that come to mind.
What's the database for?
What's going to be stored in it, for how long and why?
Who's going to be using this and how?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 24, 2007 at 2:24 am
Hi karthikeyan,
when we think abount DB Design the 1st trigger comes in mind is DDL trigger on database.
Trigger depends on the requirements.
November 24, 2007 at 10:44 am
"3rd Normal Form"
Heh... Look it up 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
November 25, 2007 at 11:21 pm
whcih one should comes to mind ?
3 rd normal form
(or)
What's the database for?
What's going to be stored in it, for how long and why?
Who's going to be using this and how?
(or)
both
karthik
November 25, 2007 at 11:39 pm
absolutly correct karthikeyan, but beforeDB design we need to analyze do we really need a trigger. If yes the for what purpose we will desing it , trigger may be on table or on database (in Sql 2005 ) so it depends upon the user as well as on developer also.
If suppose DB dev. want to maintain DDL log audit on database then will write a DB trigger.
if want to maintain any table audit will create trigger on tables.
November 26, 2007 at 8:52 am
Ahbijit,
Karthick doesn't need a "trigger"... what he said was...
I want to know about Database design. If we think about 'DataBase Design', which would trigger first in our mind ?
... and where he said "which would trigger first in our mind?", he meant "What should be the first thing we take into consideration when designing a database?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 26, 2007 at 8:57 am
3 rd normal form
(or)
What's the database for?
What's going to be stored in it, for how long and why?
Who's going to be using this and how?
(or)
both
Both... you cannot design a database in correct 3rd normal form without knowing the rest "what's going to be stored in it".
The "how long" and "who's going to be using this and how" actually doesn't matter much. Whether it's for a GUI or Batch File processing doesn't matter because it should always be able to do both.
If you're talking a "data mart", then that's something different. Frequently, they are both pre-summarized and somewhat "de-normalized" for performance reasons.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 26, 2007 at 11:22 am
Jeff Moden (11/26/2007)
3 rd normal form
(or)
What's the database for?
What's going to be stored in it, for how long and why?
Who's going to be using this and how?
(or)
both
Both... you cannot design a database in correct 3rd normal form without knowing the rest "what's going to be stored in it".
The "how long" and "who's going to be using this and how" actually doesn't matter much. Whether it's for a GUI or Batch File processing doesn't matter because it should always be able to do both.
If you're talking a "data mart", then that's something different. Frequently, they are both pre-summarized and somewhat "de-normalized" for performance reasons.
3NF kind of entails that you know what the "database is for". Keep in mind that the single question "what's the DB for" represents a whole (often iterative) process describing in detail what the DB is for. You need to have an idea of the breadth and depth of the data you need to collect (let's call that the "universe"); you then need to organize your universe into items that naturally go together (let's call them "entities"), and then determine how your entities should relate to each other. Then, using 3NF, you then start looking at your ENTITIES, and try to make sure that THEY don't need to be broken up and organized into more granular entities, etc...
This is not a single question anymore, as you can see. It's an intricate process, requiring involvement from both the business, dev and data areas in most cases.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 26, 2007 at 1:38 pm
Jeff Moden (11/26/2007)
The "how long" and "who's going to be using this and how" actually doesn't matter much.
I had a reason for writing those, but now I can't remember fully.
Hmmm.... Think it had to do with need for archiving and growth planning (how long). OLTP or decision support (ie, do we need to consider storing pre-computed averages, selective denormalisation, etc)
But those are more advanced concepts in DB design, and one of the biggest mistakes I see is people skipping intermediate steps to get to a final design.ie not bothering to normalise fully.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 27, 2007 at 6:04 am
Ok Let us assume i am going to design a database for a college or school.Basically i don't have good knowledge in DataBase Design. Although here i am going write how to design tables for a school, but i don't know whether it is correct.Asking your people to correct me.
Table1:
TableName - Admission
PrimaryKey - StudId ( AutoGeneratedNumber - Assume StudId will be generated through front end logic)
-------------------------------------------------------------------------------
StudId StudentName FatherName/MomName Sex Nationality Religion Age Class Section Remarks
--------------------------------------------------------------------------------
Table2:
TableName - Address
ForeignKey - StudId depends Admission table StudId column
---------------------------------------------------------------------------
StudId Address1 Address2 Address3 City Country Phone Mobile Email Comments
---------------------------------------------------------------------------
Table3:
FeeDetails
ForeignKey - StudId depends Admission table StudId column
---------------------------------------------------------------------------
StudId JoiningFee SpecialFee OtheerFee Remarks
---------------------------------------------------------------------------
Just i designed some sample tables. is this called database design ?I know there are lot of things in Database design. So My question is,Is this called basic database design ?
As your people said,
1) Need to take a look into each and every table whether it satisfies 3NF.
2) Need to decide DB Size
3) Assume this DB would not be used by more than 15 peoples.
Am i Correct ? If not please correct wherever i made mistakes.
karthik
November 27, 2007 at 7:17 am
karthikeyan,
There is one thing I don't understand. Your title is Senior Software Engineer and what kind of work you are doing so far. It seems you do not understand too much about database but you want to learn it in one day. You asked a lot of basic questions and if you are senior engineer, you should know them already.
Database design is largely based on experiences, even you read all the database design books, without real life experiences, it is no use.
November 27, 2007 at 7:30 am
I was working on a software company that sold high education software.
Table - Student
StudentID - automatically generated
Social Security number
Student Name
Birthday
Gender
Nationality
Race
Email Address
Father Name
Mother Name
Table - StudentAddress
AddressID - Auto generated
StudentID - FK to Student table
Address Type (Permanent, Billing....)
Address1
Address2
City
State/Provence
Zipcode
Country
Telephone Number
Table - StudentBilling
StudentBillingID - Auto Generated
StudentID - FK to Student Table
Term (Fall 2007, Winter 2007....)
Number of credit
Tuition
Other charges
Table - StudentAdmission
AdmissionID - Auto Generated
StudentID - FK to Student Table
Term Applied (Fall 2007, Fall 2008...)
Student Application (Freshman, Transfer)
Application Status (Accept, Declined, Waitlist)
Transcript_Recieved (Yes/No)
Reference_letter_received (Yes/No)
my 2 cents
November 27, 2007 at 5:24 pm
Karthik,
No... that's what a lot of folks do and it's not database design. For starters, each table should have a primary key (as well as having a clustered index which may not show on such plans). You've mixed the "Next of Kin" information with the student information on the same row and called it by the wrong name "Admission". You mixed class information with student information on the same row, as well. I see no sign of an audit plan nor planned indexes for performance. I see no generic data type information nor planned data constraints. I see only a minor attempt at a naming convention. I see no "User" table to control access via a gui and I see no table for Class information. Most of the tables in your example are pretty much denormalized and that's not good. I could go on, but I think you get the idea.
Database design isn't just throwing together a handfull of databases and calling it done. Sure, you might be able to get away with it for a while... but in the face of scalability, performance, and reporting requirements, such nonchalant attempts at design will simply not stand the test of time.
So, where do you begin learning about proper database design? Just like I said... the first thing you should learn is about "normalization". Look that word up in Books Online and study it. Do it now before you learn the wrong way to do things... it'll be tougher to "unlearn what you have learnt".
--Jeff Moden
Change is inevitable... Change for the better is not.
November 29, 2007 at 2:29 am
Jeff,
Thanks for your proper explanation.
As you told, i have started to study about 'Normalization'.
karthik
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply