October 22, 2012 at 7:57 am
hi,
i need to develope new system,for that i need to design new databases in my system.
can anybody tell me where to start, for example,do i need to do manually or creating logical and physcial data model.
what is the best approach.
thanks
October 22, 2012 at 9:14 am
I am not saying this sarcastically, but honestly your best approach is to outsource this work to someone who knows what they are doing with respect to SQL and database design.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 22, 2012 at 9:35 am
well.
its not in my hand,my company wants me to do it
October 22, 2012 at 9:41 am
harri.reddy (10/22/2012)
well.its not in my hand,my company wants me to do it
Then you need to figure out what you need to store. What are the entities in this system? What attributes do these entities have. What is the relationship of these entities?
I would recommend getting some books on data structures and normalization. The better your architecture is the easier it will be to work with. If the structures are not a good fit for the process then it will be a constant uphill battle.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 22, 2012 at 9:50 am
People spend years studying this topic and you don't get good at it overnight. There are basic rules you can learn from books and there are lots of different ones available (Google is your friend), including a Database Design for Dummies book.
But lack of experience can hurt you badly here. There are always tradeoffs in any design work and knowing how to work those tradeoffs intelligently into the design is critical for anything but the most trivial application. I'd second the notion that you need someone with greater experience to, at least, act as a mentor to you on this. For specific design questions, you can always come back to these forums, but working start-to-finish on a design project is not really effective on this type of media.
Any chance your employer might spring for some training before they throw you into the deep end of the pool?
October 22, 2012 at 10:06 am
guys,
they gave me docs,which field they need and what lenght of data type.
my question is ,should i create logical /physical data model or start making table in sql server manually.
let me know howo to start
October 22, 2012 at 10:10 am
harri.reddy (10/22/2012)
guys,they gave me docs,which field they need and what lenght of data type.
my question is ,should i create logical /physical data model or start making table in sql server manually.
let me know howo to start
Sounds to me like they handed you the model and said, make our database.
Create table ...
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 22, 2012 at 10:15 am
guys,
they gave me docs,which field they need and what lenght of data type.
my question is ,should i create logical /physical data model or start making table in sql server manually.
let me know howo to start
October 22, 2012 at 10:24 am
harri.reddy (10/22/2012)
guys,they gave me docs,which field they need and what lenght of data type.
my question is ,should i create logical /physical data model or start making table in sql server manually.
let me know howo to start
It depends!
If you designing just a small database with couple of tables in it for few people to use, you can well just go and "start making table in sql server manually". Even in such simple case, bad design will surely give you a lot of troubles in a future...
If it's anything more serious, then designing of such database will require much more than just "create logical /physical data model". So, even if anyone will tell you that you should "create logical /physical data model" it will be no much help to you!
October 22, 2012 at 11:04 am
They gave you fields & data lengths, cool. But are they defined as tables or as objects or just as interesting pieces of information that the business wants to see? You're goal is turn everything into tables. One approach to that is to first create a series of logical models. The idea behind these is to play with the structures before you map them all out to tables with constraints, etc., because the models are quick & easy to rearrange.
Assuming you are looking at objects or interesting pieces of information, then you need to go through a process called normalization to arrive at the initial logical models. This will break down the data into sets based on a series of logical operators called the Rules of Normalization. I've seen anywhere from 3 to 9 different rules, but most of the time you can get by on just the 3. This will get you started there.
You're in a tough spot if you've never done this before. I'd suggest some very heavy reading. I love Louis Davidson's work, so he's a must. I'd also suggest Clare Churcher's book.
This topic is just huge. Two things, we won't be able to give you enough information to really get you going based on answers here, but if you get started and get stuck, come back to the Design forum and post your specific questions. Then you can get specific answers. Second, you're going to make mistakes as you do this. Assume it. Communicate it to others, then do your best.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 22, 2012 at 11:49 am
well,
they gave me some tables(approx 9) tables and field that they want to fecth only those field.
they all r master table.so do i need to make table that connect this table.like creating third table that shows relation between two tables.
my application is like this,w have UI in which user will enter data and as a backend that data will store in
database that i need to design
also i have question ,for example
i have patient table and clinic table
but they dont have relation, i need to create third table patient_clinic that connect this two table.
so if user enter information on front end, how this will store in this third table patient_clinic,
how to create logical model,what tool you prefer, please help me
October 22, 2012 at 12:00 pm
harri.reddy (10/22/2012)
well,they gave me some tables(approx 9) tables and field that they want to fecth only those field.
they all r master table.so do i need to make table that connect this table.like creating third table that shows relation between two tables.
my application is like this,w have UI in which user will enter data and as a backend that data will store in
database that i need to design
also i have question ,for example
i have patient table and clinic table
but they dont have relation, i need to create third table patient_clinic that connect this two table.
so if user enter information on front end, how this will store in this third table patient_clinic,
how to create logical model,what tool you prefer, please help me
There just isn't a "this is the way to do this" kind of answer to this. It all boils down to the specifics of what the system does and you haven't actually provided anything other than very vague and generic descriptions. The scope of this is WAY more than an online forum can help with. You just can't cram several years worth of knowledge and experience into a few posts on an online forum. There are literally thousands and thousands of books on this topic. You seriously need to talk to your boss and ask about getting a consultant for this project and training for you to be able to do this in the future.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 22, 2012 at 2:04 pm
I agree, this is way too big for a quick question.
I'd also be willing to bet that much of what they gave you is objectively not designed very well, if at all.
You need to start from scratch with a logical design ... or, if they won't give you time for that, just do the best you can and try NOT to own the resulting db :-).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 22, 2012 at 2:53 pm
Sean Lange (10/22/2012)
harri.reddy (10/22/2012)
well,they gave me some tables(approx 9) tables and field that they want to fecth only those field.
they all r master table.so do i need to make table that connect this table.like creating third table that shows relation between two tables.
my application is like this,w have UI in which user will enter data and as a backend that data will store in
database that i need to design
also i have question ,for example
i have patient table and clinic table
but they dont have relation, i need to create third table patient_clinic that connect this two table.
so if user enter information on front end, how this will store in this third table patient_clinic,
how to create logical model,what tool you prefer, please help me
There just isn't a "this is the way to do this" kind of answer to this. It all boils down to the specifics of what the system does and you haven't actually provided anything other than very vague and generic descriptions. The scope of this is WAY more than an online forum can help with. You just can't cram several years worth of knowledge and experience into a few posts on an online forum. There are literally thousands and thousands of books on this topic. You seriously need to talk to your boss and ask about getting a consultant for this project and training for you to be able to do this in the future.
I would just throw away the big word "Logical Model" in the trash bin and just get your hand dirty by create the table patient_clinic, or add a new field call clinic_id to the patient table....all depends on what kind of relationship will be used, whether it is 1-many, or many-to-many, etc. then redesign your table around to suit the requirements. If you hit the deadend, come back here and ask again 🙂
October 22, 2012 at 7:06 pm
harri.reddy (10/22/2012)
well,they gave me some tables(approx 9) tables and field that they want to fecth only those field.
they all r master table.so do i need to make table that connect this table.like creating third table that shows relation between two tables.
my application is like this,w have UI in which user will enter data and as a backend that data will store in
database that i need to design
also i have question ,for example
i have patient table and clinic table
but they dont have relation, i need to create third table patient_clinic that connect this two table.
so if user enter information on front end, how this will store in this third table patient_clinic,
how to create logical model,what tool you prefer, please help me
From what you're saying, and remember, we're not there and can't see or know what's in front of you, you have a pre-defined set of tables. There is no "design" to do. Create the tables you're being asked to create.
As to the details of making a relationship between tables that don't have a direct one, you need to identify what it is that relates these two tables. Is it the primary key of each table? There's a thing called an interim table, also known as many-to-many, that contains the key from each of the other two tables, probably as the primary key. Then you can relate any one of the first table to any one of the second, and multiples from either direction, but not having more than one matched pair. But, that assumes a relationship that you have yet to establish.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply