SET NOCOUNT ON

CREATE TABLE [CASE]
(
	CASE_NR INT IDENTITY(1,1) PRIMARY KEY
	-- case specifics
)

CREATE TABLE EXHIBIT
(
	CASE_NR INT NOT NULL REFERENCES [CASE](CASE_NR)
	,EXHIBIT_NR INT IDENTITY(1,1) PRIMARY KEY
	-- exhibit specifics
)

CREATE TABLE ITEM
(
	CASE_NR INT NOT NULL REFERENCES [CASE](CASE_NR)
	,ITEM_NR INT IDENTITY(1,1) PRIMARY KEY
	,EXHIBIT_NR INT NULL REFERENCES EXHIBIT(EXHIBIT_NR)
	-- common to system & media
)

CREATE TABLE SYSTEM_ITEM
(
	ITEM_NR INT NOT NULL PRIMARY KEY REFERENCES ITEM(ITEM_NR)
	-- system specifics
)

CREATE TABLE MEDIA_ITEM
(
	ITEM_NR INT NOT NULL REFERENCES ITEM(ITEM_NR) PRIMARY KEY
	,ITEM_NR_SYSTEM INT NULL REFERENCES SYSTEM_ITEM(ITEM_NR)
	-- media specifics
)

-- Add some sample data
INSERT [CASE] DEFAULT VALUES

-- Add two MEDIA
INSERT ITEM(CASE_NR) SELECT IDENT_CURRENT('[CASE]'); INSERT MEDIA_ITEM(ITEM_NR) VALUES(SCOPE_IDENTITY())
INSERT ITEM(CASE_NR) SELECT IDENT_CURRENT('[CASE]'); INSERT MEDIA_ITEM(ITEM_NR) VALUES(SCOPE_IDENTITY())

-- Add two SYSTEM
INSERT ITEM(CASE_NR) SELECT IDENT_CURRENT('[CASE]'); INSERT SYSTEM_ITEM(ITEM_NR) VALUES(SCOPE_IDENTITY())
INSERT ITEM(CASE_NR) SELECT IDENT_CURRENT('[CASE]'); INSERT SYSTEM_ITEM(ITEM_NR) VALUES(SCOPE_IDENTITY())

-- Add two MEDIA related to a SYSTEM
DECLARE @item_nr INT
INSERT ITEM(CASE_NR) SELECT IDENT_CURRENT('[CASE]')
SET @item_nr = SCOPE_IDENTITY()
INSERT SYSTEM_ITEM(ITEM_NR) VALUES(@item_nr)
INSERT ITEM(CASE_NR) SELECT IDENT_CURRENT('[CASE]'); INSERT MEDIA_ITEM(ITEM_NR,ITEM_NR_SYSTEM) VALUES(SCOPE_IDENTITY(),@item_nr)
INSERT ITEM(CASE_NR) SELECT IDENT_CURRENT('[CASE]'); INSERT MEDIA_ITEM(ITEM_NR,ITEM_NR_SYSTEM) VALUES(SCOPE_IDENTITY(),@item_nr)


PRINT 'All SYSTEMS...'
SELECT i.* FROM ITEM i JOIN SYSTEM_ITEM si ON i.ITEM_NR = si.ITEM_NR
PRINT 'All MEDIA...'
SELECT i.*,mi.ITEM_NR_SYSTEM FROM ITEM i JOIN MEDIA_ITEM mi ON i.ITEM_NR = mi.ITEM_NR


DROP TABLE MEDIA_ITEM,SYSTEM_ITEM,ITEM,EXHIBIT,[CASE]

quote:

---

Originally posted by iwaters

rockmoose thanks for that, it makes sense and works well for me.

You mention putting 2 bridge tables into to remove the NULLS. Would these go between the ITEM Supertype and SYSTEM_ITEM/MEDIA_ITEM subtypes? Would the bridge table contain a composite key of EXHIBIT_NR and ITEM_NR, or have I got that completly wrong?

---

CREATE TABLE EXHIBIT_ITEM
(
	EXHIBIT_NR INT NOT NULL REFERENCES EXHIBIT(EXHIBIT_NR)
	,ITEM_NR INT NOT NULL REFERENCES ITEM(ITEM_NR)
	,PRIMARY KEY(EXHIBIT_NR,ITEM_NR)
	-- specifics to the relation between the exhibit and the item (system or media), such as date when the item became an exhibit
)

CREATE TABLE SYSTEM_MEDIA
(
	ITEM_NR_SYSTEM INT NOT NULL REFERENCES SYSTEM_ITEM(ITEM_NR)
	,ITEM_NR_MEDIA INT NOT NULL REFERENCES MEDIA_ITEM(ITEM_NR) PRIMARY KEY
	-- specifics to the relation between the system and item, such as reason why this media belongs to the system
)