Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ERD diagram vs. Implementation Expand / Collapse
Author
Message
Posted Thursday, March 11, 2010 8:04 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:40 PM
Points: 322, Visits: 424
I am a database administrator. I am not a data architect. I am easily confused.

I am working with a SQL Server 2005 database that has little to no existing documentation. One of the existing pieces of documentation is a relatively current ERD diagram. There are candidate key - foreign key relationships depicted in the ERD diagram.

Now for the 'easily confused' part: Am I huffing glue to expect that the candidate key - foreign key relationships depicted in the ERD diagram be implemented as foreign key definitions in the production version of the database?

The database was an Oracle database converted to SQL Server by Oracle DBAs. Not much of the declarative referential integrity came across. Most of the tables may as well be flat files, there is not much that relates the tables to one another.

No, the application in front of this database does not address these issues. Rather, the application exploits these issues.

Please let me know if I am being unreasonable to expect relationships depicted in the ERD to evidence themselves as foreign keys in the physical implementation of the database. My current situation is a lost cause, but there is another database headed towards production with much the same lack of declarative referential integrity.



Post #881031
Posted Thursday, March 11, 2010 8:11 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:32 PM
Points: 3,110, Visits: 11,529
DRI should be the default, without some overwhelming reason not to use it,

And by “overwhelming reason”, I don’t mean that the developers were too lazy to bother, didn't know what they were doing, or were sure that the application would take care of it.


Others may disagree with this, but they're wrong.


Post #881040
Posted Thursday, March 11, 2010 8:39 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:40 PM
Points: 322, Visits: 424
Thank you, Michael, for the sanity check.


Post #881067
Posted Wednesday, April 20, 2011 9:30 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 2:32 AM
Points: 451, Visits: 3,470
Bear in mind that ERDs may be created for different purposes. Sometimes the purpose of an ERD is A: to model the Universe of Discourse. Sometimes the purpose is B: to represent what should be or is being implemented in the database. People who use ERDs often refer to A as a conceptual or logical ERD and B as a physical ERD.

The declarative constraints existing in Microsoft SQL Server are not capable of implementing every commonly occurring business rule (i.e. the rules intended to ensure the UoD is modelled accurately). Some of the constraints specified or implied by an ERD therefore might not be implemented. Some constraints might be implemented but only as procedural code (e.g. triggers) and some might be impossible or impractical to implement in the database at all. Implementing business rules in a DBMS (SQL ones generally, not just SQL Server) is always a compromise and quite often more integrity rules have to be left out than are put in.

Oracle has more referential integrity options than SQL Server has (e.g. deferred constraints, full/parial matching). Also the semantics of nullable uniqueness constraints in Oracle are different to SQL Server, which also means the semantics of RI constraints referencing those uniqueness constraints can be different too. So someone with an Oracle background might think it worth putting certain constraints into his ERD and only later find that they cannot be implemented in SQL Server in the way the model described.


David
Post #1096430
Posted Wednesday, April 20, 2011 11:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:26 PM
Points: 5,466, Visits: 7,647
Start with: Foreign Keys are useful and effective. They should be there if you don't have a reason not to have them there. No argument.

However, FK's stop truncates, amongst other things. They are a harassment if you don't need them because of sloppy front end code and a lack of transactional procs. How often do you truncate tables? Depends on design. Yes, usually you'd only do it in staging tables, or on report servers to quickly reload smaller tables via a snapshot push, which is where you'd usually reference from your larger tables that you DON'T truncate. I know there are alternatives, they're just not as easy to maintain for a simple task.

So, yes, you probably want them. I don't necessarily consider them the be all end all to the ERD though.

For anyone who wants to prove the truncate problem to themselves:

CREATE TABLE ABC (ParentID INT IDENTITY( 1, 1) NOT NULL, somedata VARCHAR(50))
ALTER TABLE ABC ADD CONSTRAINT PK_ABC PRIMARY KEY CLUSTERED (ParentID)

INSERT INTO ABC (somedata) VALUES ( 'abc')
INSERT INTO ABC (somedata) VALUES ('def')

CREATE TABLE childABC (ChildID INT IDENTITY( 1, 1) NOT NULL, ParentID INT NOT NULL, someotherdata VARCHAR(50))

INSERT INTO childABC (ParentID, someotherdata) VALUES ( 1, 'uuu')
INSERT INTO childABC (ParentID, someotherdata) VALUES ( 1, 'iii')
INSERT INTO childABC (ParentID, someotherdata) VALUES ( 2, 'bbb')
INSERT INTO childABC (ParentID, someotherdata) VALUES ( 2, 'vvv')

ALTER TABLE childABC
ADD CONSTRAINT FK_childABC_ParentID FOREIGN KEY (ParentID) REFERENCES ABC (ParentID)

select * from childABC

TRUNCATE TABLE childABC
TRUNCATE TABLE ABC

/*
Error running the last statement:
Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'ABC' because it is being referenced by a FOREIGN KEY constraint.
*/




- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1096546
Posted Sunday, April 24, 2011 3:36 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
SethT3 (3/11/2010)
The database was an Oracle database converted to SQL Server


It appears to be the case of a half-cooked conversion - nothing more, nothing less.


_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #1097941
Posted Sunday, April 24, 2011 8:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,769, Visits: 32,437
SethT3 (3/11/2010)
I am a database administrator. I am not a data architect. I am easily confused.

I am working with a SQL Server 2005 database that has little to no existing documentation. One of the existing pieces of documentation is a relatively current ERD diagram. There are candidate key - foreign key relationships depicted in the ERD diagram.

Now for the 'easily confused' part: Am I huffing glue to expect that the candidate key - foreign key relationships depicted in the ERD diagram be implemented as foreign key definitions in the production version of the database?

The database was an Oracle database converted to SQL Server by Oracle DBAs. Not much of the declarative referential integrity came across. Most of the tables may as well be flat files, there is not much that relates the tables to one another.

No, the application in front of this database does not address these issues. Rather, the application exploits these issues.

Please let me know if I am being unreasonable to expect relationships depicted in the ERD to evidence themselves as foreign keys in the physical implementation of the database. My current situation is a lost cause, but there is another database headed towards production with much the same lack of declarative referential integrity.


You say that not much of the DRI came across from the Oracle conversion and, I suspect, there wasn't much there to come across. I agree with what the others have stated... DRI on a production database is an absolute must and you should be able to generate new physical ERDs from it.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1097955
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse