March 17, 2014 at 2:12 pm
Greg Edwards-268690 (3/17/2014)
Steve Jones - SSC Editor (3/17/2014)
Lots of repetition in the explanations here. Anyone have a good entity example that shows the difference? As I explain this, it sounds as though "more detail in the logical model" is what everyone thinks, but I'm not sure that's a good definition.See if this[/url] is clear enough.
There is overlap. Each adds to the picture, where physical gets into the specific platform.
Ugh, I think that's worse as it's poorly structured in English.
Really it needs an example that shows a specific model. Take a PO or invoice. What's the conceptual, logical/physical. In my mind, I think I'm not sure what's the difference between the conceptual and logical.
Conceptual:
A purchase order has a header listing
- PO number (identifier)
- Customer
- date
- terms
It also has details that explain the items being purchased
- item (identifier)
- description
- quantity
- cost
Logical:
Not sure what's different.
Physical:
PO HEader
- PO Number - varchar, unique, PK
- Customer ID - int FK to customer details
- PODate - datetime
- Terms - int - FK to term lookup
PODetails
- PO Number - int - FK to PO Header
- Line Item - int
- ProductID int - FK to product information
- Quantity - int
- Cost - numeric
March 17, 2014 at 3:34 pm
Wikipedia has a table that has a good outline of some of the key differences.
Maybe this explains it better.
I kind of like the breakdown Zachman is making.
Conceptual... Logical... Physical. Anyone in manufacturing or construction would recognize the Owner's requirements, the Engineer's design and the Manufacturing Engineer's design... conceptually what you are trying to produce... logically how it would be designed... and physically how you intend to build it.
Somewhat here's what the Home Owner has a vision of
then what the Architect draws
and then how the Carpenter builds it.
March 17, 2014 at 3:48 pm
Luis Cazares (3/16/2014)
andrew gothard (3/16/2014)
GilaMonster (3/16/2014)
I have two clients where I'm writing up a performance report which includes an entire section on their use of NoLock. Going to be an interesting weekPHB "I've decided to make NOLOCK a standard, we're going to put it in every query." "No we're not". "I'm the Boss, I'm playing the boss card, we are". "Nope, not going to happen" <Hissy>, "we need the program to be fast!". "And inaccurate?", "YES, IF Needs be, we need to be Agile". "What part of the Agile Manifesto recommends inaccurate results, I missed that bit ... "
Funnily enough, he was actually a lovely guy.
I wonder why these people suggest to use NOLOCK on every query but won't ever suggest to use read uncommited isolation level. That only shows how little they know about this hint and its effects.
The problem is, and this is possibly going to sound a bit arsey, once upon a time you only survived in this game if you could understand how to build a scalable system that met business needs. Unless you were EDS - of course.
People now can get by, indeed have a cushy career, slinging together pretty looking websites that get a dozen hits a month where any old crap really will do. And it will, any moron can put together any old crap for Mon n' Pop's Corner Shop. Then put "Developed x websites" and get taken on as a developer, or even a Dev Manager.
And they simply don't know what they don't know - even when it's pointed out to them, even when it's nothing more than common sense. "I've never had to bother about this before - I've seen this on a website, it's Majik Pixie Dust". That's not slagging Devs in general, I come from a Dev background. Most are great. Some, just aren't.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
March 17, 2014 at 4:12 pm
Steve Jones - SSC Editor (3/17/2014)
Had an interesting question from my kid last night. He is studying for a GIS test and was confused about the phases of database design- conceptual
- logical
- physical
I found a few references (http://www2.amk.fi/digma.fi/www.amk.fi/opintojaksot/0303011/1146161367915/1146161680673/1146161874577/1146161968355.html), but couldn't give him good examples of how #1 and #2 differentiate. I've tended to blur them together in my past.
Any succinct explanations?
Conceptual - what business processes are you capturing in the system. What are people doing, what do they need to track, what information do you need to store to expedite this.
Logical - how is this list of data elements structured - what is the normalised form of the collection of data elements. How are your tables related to each other. Which normal form do you require? What keys and constraints help describe your data. This could then be applied to any DBMS
Physical (this mostly from a purely relational perspective, but applies in part to other DBMS) - how is the logical design implemented on the database system you're using. This would include aspects such as appropriate indexing, filegroups, scale out / up considerations (AOG / RAC / sharding design), I'd also include storage, box and memory configuration - alongside other engine tweaks (do we optimise for ad-hoc queries). Even does it go on it's own (v)Box or standalone. Where do you optimise / denormalise *appropriately*. (Your ATM experience is going to be pretty horrible if the bank goes through every transaction in the 25 years you've been with them in order to decide whether you can get 20 out, rather than keep a running total)
My take on it anyway. I do talk a lot of rubbish at times though.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
March 17, 2014 at 10:32 pm
Steve Jones - SSC Editor (3/17/2014)
Greg Edwards-268690 (3/17/2014)
Steve Jones - SSC Editor (3/17/2014)
Lots of repetition in the explanations here. Anyone have a good entity example that shows the difference? As I explain this, it sounds as though "more detail in the logical model" is what everyone thinks, but I'm not sure that's a good definition.See if this[/url] is clear enough.
There is overlap. Each adds to the picture, where physical gets into the specific platform.
Ugh, I think that's worse as it's poorly structured in English.
Really it needs an example that shows a specific model. Take a PO or invoice. What's the conceptual, logical/physical. In my mind, I think I'm not sure what's the difference between the conceptual and logical.
Conceptual:
A purchase order has a header listing
- PO number (identifier)
- Customer
- date
- terms
It also has details that explain the items being purchased
- item (identifier)
- description
- quantity
- cost
Logical:
Not sure what's different.
Physical:
PO HEader
- PO Number - varchar, unique, PK
- Customer ID - int FK to customer details
- PODate - datetime
- Terms - int - FK to term lookup
PODetails
- PO Number - int - FK to PO Header
- Line Item - int
- ProductID int - FK to product information
- Quantity - int
- Cost - numeric
Your conceptual is already a bit too low level thus it is hard to distinguish it from the logical.
At a conceptual level - a Purchase order tracks all of the information about a customer's order. It associates the order to the customer, sets up the relevant payment and shipping terms, and maintains a list of all items ordered.
At a logical level - (start breaking into what you've described as conceptual + more entities)
etc.
----------------------------------------------------------------------------------
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?
March 18, 2014 at 8:48 am
Steve, I think you've just done this too much. Once you understand what you're trying to do, Conceptual and Logical blend quite a bit, because you start conceptualizing in terms of what you know works logically. So it's really only a division for folks that are doing it for the first couple times, but is a good process to follow just to make sure you checked all the corners.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
March 18, 2014 at 9:24 am
Matt Miller (#4) (3/17/2014)
Your conceptual is already a bit too low level thus it is hard to distinguish it from the logical.At a conceptual level - a Purchase order tracks all of the information about a customer's order. It associates the order to the customer, sets up the relevant payment and shipping terms, and maintains a list of all items ordered.
At a logical level - (start breaking into what you've described as conceptual + more entities)
etc.
Not according to what I've seen in a few textbooks. Not that those are correct, but they do talk about including the entities and what information is being tracked, though from a customer/client point of view.
March 18, 2014 at 9:25 am
jcrawf02 (3/18/2014)
Steve, I think you've just done this too much. Once you understand what you're trying to do, Conceptual and Logical blend quite a bit, because you start conceptualizing in terms of what you know works logically. So it's really only a division for folks that are doing it for the first couple times, but is a good process to follow just to make sure you checked all the corners.
That's probably true. Typically I've worked with customers to knock out the logical model together and that will often translate closely to the physical.
March 18, 2014 at 10:09 am
Steve Jones - SSC Editor (3/18/2014)
jcrawf02 (3/18/2014)
Steve, I think you've just done this too much. Once you understand what you're trying to do, Conceptual and Logical blend quite a bit, because you start conceptualizing in terms of what you know works logically. So it's really only a division for folks that are doing it for the first couple times, but is a good process to follow just to make sure you checked all the corners.That's probably true. Typically I've worked with customers to knock out the logical model together and that will often translate closely to the physical.
Funny how what I read here correlates to discussions I'm having at work. My new workplace is dealing with issues where BA's are going too deep into logical/physical design instead of letting the tech people do their jobs. Then the place is ending up with issues because people who don't understand databases are making decisions they shouldn't be.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 18, 2014 at 10:36 am
Steve Jones - SSC Editor (3/18/2014)
jcrawf02 (3/18/2014)
Steve, I think you've just done this too much. Once you understand what you're trying to do, Conceptual and Logical blend quite a bit, because you start conceptualizing in terms of what you know works logically. So it's really only a division for folks that are doing it for the first couple times, but is a good process to follow just to make sure you checked all the corners.That's probably true. Typically I've worked with customers to knock out the logical model together and that will often translate closely to the physical.
I'm not sure if it'll still help, but there are some main differences between logical and conceptual design.
- Conceptual design works with entities and attributes, while logical design works with tables and columns.
- Conceptual design is completely independent of the DBMS, while logical design is focused on a specific DBMS.
- Attributes don't have data types assigned, while columns do.
- While you might be able to identify candidate keys on the conceptual design, you'll define the PKs and FKs in the logical design.
- Relations in the Conceptual design can be transformed into tables in the logical design (mostly for many-to-many relations).
Most people will skip conceptual design and start at the logical design (some might even start with the physical design and we all know how that ends up).
March 18, 2014 at 11:27 am
Luis Cazares (3/18/2014)
Steve Jones - SSC Editor (3/18/2014)
jcrawf02 (3/18/2014)
Steve, I think you've just done this too much. Once you understand what you're trying to do, Conceptual and Logical blend quite a bit, because you start conceptualizing in terms of what you know works logically. So it's really only a division for folks that are doing it for the first couple times, but is a good process to follow just to make sure you checked all the corners.That's probably true. Typically I've worked with customers to knock out the logical model together and that will often translate closely to the physical.
I'm not sure if it'll still help, but there are some main differences between logical and conceptual design.
- Conceptual design works with entities and attributes, while logical design works with tables and columns.
- Conceptual design is completely independent of the DBMS, while logical design is focused on a specific DBMS.
- Attributes don't have data types assigned, while columns do.
- While you might be able to identify candidate keys on the conceptual design, you'll define the PKs and FKs in the logical design.
- Relations in the Conceptual design can be transformed into tables in the logical design (mostly for many-to-many relations).
Most people will skip conceptual design and start at the logical design (some might even start with the physical design and we all know how that ends up).
Logical does not deal with DBMS, Physical does.
Data Types tend to get more into the Physical layer also.
One pattern is obvious - when you have done database work, you are always diving down to the next level.
So many of us tend to try and head off potential issues in the conceptual phase.
Conceptual is big picture, and each level gets more specific down until you are dealing with the target DBMS.
I still tend to think that the Conceptual level is more of what you and the person with the requirements will talk about.
They have a requirement that can be white boarded out without getting into real details.
General relationships, which may be broken down further as you get into the design.
In Conceptual, you do not specify how you would ensure uniqueness for updates.
In Logical, you will. And the person you are discussing Conceptual is not usually a DBA, so they do not care, and it may confuse them.
Also in Logical, you get into the specifics of the relationships between the tables.
Not a real part of the Conceptual level. They only might care they have a Customer entity, which also will have Contact info, address, etc.
That will likely be broken down in the logical level.
A good example might be Phone Number for a Customer.
It can have several different types (main, Cell, Fax), which are in the Logical level, but might not even be mentioned in the Conceptual level.
Physical will include performance considerations, while Logical does not.
And Physical is specific to the DBMS design to be implemented.
March 18, 2014 at 11:37 am
Greg Edwards-268690 (3/18/2014)
Luis Cazares (3/18/2014)
Steve Jones - SSC Editor (3/18/2014)
jcrawf02 (3/18/2014)
Steve, I think you've just done this too much. Once you understand what you're trying to do, Conceptual and Logical blend quite a bit, because you start conceptualizing in terms of what you know works logically. So it's really only a division for folks that are doing it for the first couple times, but is a good process to follow just to make sure you checked all the corners.That's probably true. Typically I've worked with customers to knock out the logical model together and that will often translate closely to the physical.
I'm not sure if it'll still help, but there are some main differences between logical and conceptual design.
- Conceptual design works with entities and attributes, while logical design works with tables and columns.
- Conceptual design is completely independent of the DBMS, while logical design is focused on a specific DBMS.
- Attributes don't have data types assigned, while columns do.
- While you might be able to identify candidate keys on the conceptual design, you'll define the PKs and FKs in the logical design.
- Relations in the Conceptual design can be transformed into tables in the logical design (mostly for many-to-many relations).
Most people will skip conceptual design and start at the logical design (some might even start with the physical design and we all know how that ends up).
Logical does not deal with DBMS, Physical does.
Data Types tend to get more into the Physical layer also.
...
It depends, Logical does not deal directly with a DBMS but might be oriented towards a specific DBMS. Data types will be present in both logical and physical. In logical design you define if it's a string, numeric, date or something else, while in the physical you will define length, precision and the exact data type.
But perhaps, I might be wrong. 😀
March 18, 2014 at 11:54 am
Jack Corbett (3/18/2014)
Steve Jones - SSC Editor (3/18/2014)
jcrawf02 (3/18/2014)
Steve, I think you've just done this too much. Once you understand what you're trying to do, Conceptual and Logical blend quite a bit, because you start conceptualizing in terms of what you know works logically. So it's really only a division for folks that are doing it for the first couple times, but is a good process to follow just to make sure you checked all the corners.That's probably true. Typically I've worked with customers to knock out the logical model together and that will often translate closely to the physical.
Funny how what I read here correlates to discussions I'm having at work. My new workplace is dealing with issues where BA's are going too deep into logical/physical design instead of letting the tech people do their jobs. Then the place is ending up with issues because people who don't understand databases are making decisions they shouldn't be.
I used to run into this all the time, especially when someone wanted something done in Excel.
I remember one time, I told them something wouldn't work the way they thought it would, gave them the reasoning why not, and continued to make sure I understood what they needed.
Then I left them to let them follow their attempt at the solution, going upstairs to code my solution.
About 2 hours later, went back downstairs.
I had a prototype of exactly what they needed.
They had spent most of the time trying to do what I had said would not work.
Ultimately, that was a good thing. Playing with the data, they were able to see my reasoning.
So next time they weren't as inclined to tell me how to do it, but more into this is what we need.
Not an uncommon thing - everyone wants to learn, and create the next great thing.
Sometimes they have good ideas, and sometimes they are totally out of their league.
Some will get better over time, others may never make it to the next level.
But they will never get anywhere unless they try.
March 18, 2014 at 12:00 pm
Luis Cazares (3/18/2014)
Greg Edwards-268690 (3/18/2014)
Luis Cazares (3/18/2014)
Steve Jones - SSC Editor (3/18/2014)
jcrawf02 (3/18/2014)
Steve, I think you've just done this too much. Once you understand what you're trying to do, Conceptual and Logical blend quite a bit, because you start conceptualizing in terms of what you know works logically. So it's really only a division for folks that are doing it for the first couple times, but is a good process to follow just to make sure you checked all the corners.That's probably true. Typically I've worked with customers to knock out the logical model together and that will often translate closely to the physical.
I'm not sure if it'll still help, but there are some main differences between logical and conceptual design.
- Conceptual design works with entities and attributes, while logical design works with tables and columns.
- Conceptual design is completely independent of the DBMS, while logical design is focused on a specific DBMS.
- Attributes don't have data types assigned, while columns do.
- While you might be able to identify candidate keys on the conceptual design, you'll define the PKs and FKs in the logical design.
- Relations in the Conceptual design can be transformed into tables in the logical design (mostly for many-to-many relations).
Most people will skip conceptual design and start at the logical design (some might even start with the physical design and we all know how that ends up).
Logical does not deal with DBMS, Physical does.
Data Types tend to get more into the Physical layer also.
...
It depends, Logical does not deal directly with a DBMS but might be oriented towards a specific DBMS. Data types will be present in both logical and physical. In logical design you define if it's a string, numeric, date or something else, while in the physical you will define length, precision and the exact data type.
But perhaps, I might be wrong. 😀
Agreed - it can depend. Some might vary depending on how many DBMS platforms you deal with.
Last shop I dealt with DB2, Oracle, Sybase, and SQL.
March 18, 2014 at 1:30 pm
1 hour and 15 minutes until vacation. I am ready!!! We are taking the kids on a surprise trip to Branson for a few days. See you all next week.
_______________________________________________________________
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/
Viewing 15 posts - 43,381 through 43,395 (of 66,742 total)
You must be logged in to reply to this topic. Login to reply