L' Eomot Inversé (1/11/2013)
I haven't done any database design that really matters for a few years, but I would do something very different from your idea.Lets start with fundamental basics.
I want the design to be fairly proof against new releases of the RDBMS software, in this case SQL Server; and I want not to have it preclude the use of recently added (eight years ago?) features by using bizarre configuration. So I would change this block of code to have ON in every case where tyou have OFF.
ALTER DATABASE [PhysVirtTest] SET ANSI_NULL_DEFAULT OFF...I'm not sure that I haven't included a single ine that could sensibly have OFF, but certainly the vast majority should have ON.
Then why are there two tables (virtual objects and physical objects) instead of one? It doesn't help at all, and it does hinder! Also, what is the attitude to NULL? It seems to me that avoiding NULL is the only imaginable reason for having two tables instead of one, but your schema has NULLs anyway. Depending on how NULL-averse you are you might have more than one table, but the extra tables over and above the basic one table would be link tables, not base object tables.
So I would end up with something like
CREATE TABLE dbo.PhysorVirtEx( ID INT IDENTITY(1,1) NOT NULL Primary key Clustered
,ParentID INT NULL REFERENCES PhysorVirtEx(ID),
,Data VARCHAR(32)
,IsVirtual BIT NOT NULL,
,CONSTRAINT CKOnePar1 CHECK (ParentID is not NULL or Isvirtual = 0)
,CONSTRAINT CKVDup1 CHECK (VirtParentID <> ID)
);As you can see, I'm not so null-averse as to be silly about it.
First, thank you very much for responding.
The ANSI settings were 100% the defaults SQL 2012 gave because I didn't care about the sample database, but I wanted one for your ease in cleaning up after helping me.
I'll edit the first post to add this information about why two tables:
Physical and Virtual objects have mostly very different data, which in a real table will be sets of columns - the "Data" field here is merely a placeholder for simplicity. This is why I have two tables, so that non-key fields are facts about the key.
I apologize for not including that information originally.
opc.three (1/13/2013)
Physical objects have at most one descendent virtual object row.
Virtual objects have zero or more descendent virtual object row(s).
Which might be more common, someone asking to which physical object does this virtual object relate?? or which virtual objects refer to this physical object?.
One more question, if a physical object has 10 virtual descendants which would be more common, a separation of 10 levels between the physical object and the leaf-level virtual object (i.e. each virtual object has only one descendant) or a separation of far less that 10, maybe 2 (e.g. physical object has one virtual child and that virtual object has 9 virtual object children).
Thank you for responding as well!
I expect that going from physical to virtual, and from virtual to physical, will be more or less equally common. Both will convey valuable and necessary data for certain tasks, and all of those tasks will be required on a day to day basis.
Most often, a physical object will have from 0 to 6 direct virtual descendants at a leaf level (i.e. the first level down). Rarely, a physical object will have a completely unknown tree structure going 2-N levels down, where N is in actuality unbounded, but almost certainly less than 5. These cases cannot be easily predicted in advance - perhaps they're wide and deep, perhaps narrow and deep. I'd guess they'd be narrow and deep more often, but I cannot guarantee that.
Alas, the rare/exception cases are always a pain to deal with.