
	Home > Ask the Oracle Experts > (Archive) Database Design Questions & Answers > One-to-one-or-more relationships, revisited

Ask The Oracle Expert: Questions & Answers

EMAIL THIS

One-to-one-or-more relationships, revisited

EXPERT RESPONSE FROM: Pat Phelan

		Pose a Question

		Other Oracle Categories

		Meet all Oracle Experts
		Become an Expert for this site

Digg This!

StumbleUpon

Del.icio.us

QUESTION POSED ON: 17 January 2003
Regarding your answer to the question posed on One-to-one-or-more relationships on Nov. 13, 2002, there is a solution that would enforce referential integrity: the use of a subtype entity. For example, if there is an invoice table INVOICE(Invoice_Num PK) that stores all the invoice information, and you want to allow, but not require, details, you can create a second table for the details: INVOICE_DETAIL(Invoice_Num PK FK, Detail). This would avoid the problem of using a NULL as inapplicable, which shouldn't be done, or relying on triggers or application enforcement of integrity, which also shouldn't be done.

EXPERT RESPONSE

I don't know of any database engine that supports subtype entities through DRI.

While you are correct and many E-R products allow subtype entities, I don't know of any database engine that enforces them. Doing so would violate the Atomicity requirement of an ACID database, which would make the engine non-ACID as well as non-SQL compliant.

For More Information

Dozens more answers to tough database design questions from Pat Phelan
The Best Database Design Web Links: tips, tutorials, scripts, and more
Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
Ask your database design questions -- or help out your peers by answering them -- in our live discussion forums.
Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

Digg This!

StumbleUpon

Del.icio.us

RELATED CONTENT

	(Archive) Database Design
	Editing object-type data on the Enterprise Manager Console
	Database returning duplicate records
	Checking for corrupt blocks
	Loading PDF files using SQL*Loader
	Cycle relationships in ER diagram?
	Logical and physical data models explained
	Wide or deep?
	Is Visual FoxPro a flat database?
	Merging hotels and data
	Creating a purchase form for a hospital database

RELATED RESOURCES

2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems

Search Bitpipe.com for the latest white papers and business webcasts

Whatis.com, the online computer dictionary

Search and Browse the Expert Answer Center
Search and browse more than 25,000 question and answer pairs from more than 250 TechTarget industry experts.

Browse our Expert Advice
Operating Systems IT Management Networking Database	Security Servers and Storage Applications and Development Career Center

Oracle White Papers: Fusion Middleware

SEARCH

TechTarget provides enterprise IT professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective IT purchase decisions and managing their organizations' IT projects - with its network of technology-specific Web sites, events and magazines.


TechTarget Corporate Web Site \| Media Kits \| Reprints \| Site Map All Rights Reserved, Copyright 2003 - 2008, TechTarget \| Read our Privacy Policy