Universe Object Modeling

Discussion
1

I authored a basic universe object modeling schema for use with any ANSI compliant SQL server, specifically Microshaft SQL Server. I also took every possible THREE.JS geometry and used only properties that would apply to any and all possible geometries. So if one geometry had a property shared, it was reduced to one column. I also included a table for materials, and a table for GLB objects. I also have a renderer written that includes Keplerian orbital variables and am closing in on completing the object is what, where, when? Quaternions included with position. This way one database row has everything required to render pretty much anything I know of in the observable universe. Here is the schema:

USE [universeobjects]
GO
/****** Object: Table [dbo].[UniverseObjectGeometries] Script Date: 9/19/2025 1:49:09 AM /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UniverseObjectGeometries](
[GeometryID] [int] NOT NULL,
[UniverseObjectID] [bigint] NOT NULL,
[GeometryType] varchar NOT NULL,
[GeometryComplexity] [int] NOT NULL,
[GeometryRadius] [float] NOT NULL,
[GeometryTube] [float] NOT NULL,
[GeometryArc] [int] NOT NULL,
[GeometryDetail] [int] NOT NULL,
[GeometryLength] [int] NOT NULL,
[GeometryHeight] [int] NOT NULL,
[GeometryRadiusTop] [float] NOT NULL,
[GeometryRadiusBottom] [float] NOT NULL,
[GeometrySteps] [int] NOT NULL,
[GeometryDepth] [int] NOT NULL,
[GeometryInnerRadius] [float] NOT NULL,
[GeometryOuterRadius] [float] NOT NULL,
[GeometryThresholdAngle] [float] NOT NULL,
[GeometrySegments] [int] NOT NULL,
[GeometryXSegments] [int] NOT NULL,
[GeometryYSegments] [int] NOT NULL,
[GeometryHeightSegments] [int] NOT NULL,
[GeometryRadialSegments] [int] NOT NULL,
[GeometryTubularSegnments] [int] NOT NULL,
[GeometryCapSegments] [int] NOT NULL,
[GeometryThetaSegments] [int] NOT NULL,
[GeometryPhiSegments] [int] NOT NULL,
[GeometryBevelSegments] [int] NOT NULL,
[GeometryBevelSize] [int] NOT NULL,
[GeometryBevelOffset] [int] NOT NULL,
[GeometryBevelThickness] [int] NOT NULL,
[GeometryPhiStart] [float] NOT NULL,
[GeometryPhiEnd] [float] NOT NULL,
[GeometryThetaStart] [float] NOT NULL,
[GeometryThetaEnd] [float] NOT NULL,
[GeometryThetaLength] [int] NOT NULL,
[GeometryOpenEnded] [bit] NOT NULL,
[GeometryOrigin] varchar NOT NULL,
[GeometryP] [int] NOT NULL,
[GeometryQ] [int] NOT NULL,
CONSTRAINT [PK_UniverseObjectGeometries] PRIMARY KEY CLUSTERED
(
[GeometryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/ Object: Table [dbo].[UniverseObjects] Script Date: 9/19/2025 1:49:09 AM /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UniverseObjects](
[UniverseObjectId] [bigint] IDENTITY(1000,1000) NOT FOR REPLICATION NOT NULL,
[UniverseId] [bigint] NULL,
[UniverseObjectName] varchar NOT NULL,
[UniverseObjectDescription] [text] NOT NULL,
[UniverseObjectPositionVectorX] [float] NULL,
[UniverseObjectPositionVectorY] [float] NULL,
[UniverseObjectPositionVectorZ] [float] NULL,
[UniverseObjectDefaultXSegments] [int] NULL,
[UniverseObjectDefaultYSegments] [int] NULL,
[UniverseObjectQuaternionVectorX] [float] NULL,
[UniverseObjectQuaternionVectorY] [float] NULL,
[UniverseObjectQuaternionVectorZ] [float] NULL,
[UniverseObjectQuaternionVectorW] [float] NULL,
[UniverseObjectRadius] [float] NULL,
[UniverseObjectDefaultYRotationRate] [float] NULL,
[UniverseObjectIsSpheroid] [bit] NULL,
[UniverseObjectIsPlasma] [bit] NULL,
[UniverseObjectHasEventHorizon] [bit] NULL,
[UniverseObjectOrbitalEccentricity] [float] NULL,
[UniverseObjectArgumentOfPeriapsis] [float] NULL,
[UniverseObjectOrbitalTrueAnomoly] [float] NULL,
[UniverseObjectOrbitalOmegaLongitude] [float] NULL,
[UniverseObjectOrbitalSemiMajorAxis] [float] NULL,
[UniverseObjectOrbitalInclination] [float] NULL,
[UniverseObjectMass] [float] NULL,
[UniverseObjectEquirectangularTextureMapPath] nvarchar NULL,
[UniverseObjectNormalsMapPath] nvarchar NULL,
[UniverseObjectEquirectangularTextureMapPathLowResolution] nvarchar NULL,
[UniverseObjectEquirectangularTextureMapPathHighResolution] nvarchar NULL,
[UniverseObjectEquirectangularVectorDisplacementMapPath] [text] NULL,
[UniverseObjectUseWireframe] [bit] NULL,
[UniverseObjectWireframeColor] varchar NULL,
[UniverseObjectHasAtmosphere] [bit] NULL,
[UniverseObjectAtmosphereRadius] [float] NULL,
[UniverseObjectEquirectangularVideoTexturePath] [text] NULL,
[UniverseObjectEquirectangularVideoVectorDisplacementMapPath] nchar NULL,
[UniverseObjectAtmosphereEquirectangularVectorDisplacementMap] [text] NULL,
[UniverseObjectAtmosphereEquirectangularTextureMapPath] [text] NULL,
[UniverseObjectInitialTemporalArgument] [bigint] NULL,
[UniverseObjectParentID] [bigint] NULL,
[UniverseObjectParentDescription] [text] NULL,
[UniverseObjectGLBObjectID] [bigint] NULL,
[UniverseObjectRendererMaterialID] [bigint] NULL,
[UniverseObjectGeometryID] [int] NULL,
[UniverseObjectTypeID] [bigint] NOT NULL,
[UniverseObjectRecordBorn] [datetime] NULL,
[UniverseObjectUniverseContainerID] [bigint] NULL,
[UniverseObjectRecordCreatedByUserID] [bigint] NULL,
[UniverseObjectRecordModified] [datetime] NULL,
[UniverseObjectRecordModifiedByUserID] [bigint] NULL,
[UniverseObjectRenderResolutionType] nvarchar NULL,
[UniverseObjectCameraNear] [float] NULL,
[UniverseObjectCameraFar] [float] NULL,
[UniverseObjectCameraFOV] [float] NULL,
[UniverseObjectAmbientLightIntensity] [float] NULL,
[UniverseObjectStarLightIntensity] [float] NULL,
[UniverseObjectRender] [bit] NULL,
CONSTRAINT [PK_UniverseObjects] PRIMARY KEY CLUSTERED
(
[UniverseObjectId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/ Object: Table [dbo].[UniverseObjectsGLBObjects] Script Date: 9/19/2025 1:49:09 AM /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UniverseObjectsGLBObjects](
[GLBObjectID] [bigint] IDENTITY(100,100) NOT NULL,
[UniverseObjectID] [bigint] NOT NULL,
[GLBObjectPath] [text] NOT NULL,
[GLBObjectInitialPositionVectorX] [float] NULL,
[GLBObjectInitailPositionVectorY] [float] NULL,
[GLBObjectInitialPositionVectorZ] [float] NULL,
[GLBObjectInitialQuaternionVectorX] [float] NULL,
[GLBObjectInitialQuaternionVectorY] [float] NULL,
[GLBObjectInitialQuaternionVectorZ] [float] NULL,
[GLBObjectInitialQuaternionVectorW] [float] NULL,
[GLBObjectInitialScaleFactor] [float] NULL,
[GLBObjectOrbitalEccentricity] [float] NULL,
[GLBObjectOrbitalArgumentOfPeriapsis] [float] NULL,
[GLBObjectOrbitalTrueAnomoly] [float] NULL,
[GLBObjectOrbitalSemiMajorAxis] [float] NULL,
[GLBObjectOrbitalOmegaLongitude] [float] NULL,
[GLBObjectMass] [float] NULL,
[UniverseObjectTypeID] [bigint] NULL,
CONSTRAINT [PK_UniverseObjectsGLBObjects] PRIMARY KEY CLUSTERED
(
[GLBObjectID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/ Object: Table [dbo].[UniverseObjectsProperties] Script Date: 9/19/2025 1:49:09 AM /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UniverseObjectsProperties](
[UniverseObjectPropertyID] [bigint] NOT NULL,
[UniverseObjectID] [bigint] NOT NULL,
[UniverseObjectPropertyName] varchar NOT NULL,
[UniverseObjectPropertyValue] [text] NOT NULL,
[UniverseObjectPropertyDataType] varchar NOT NULL,
[UniverseObjectTypeID] [bigint] NOT NULL,
[UniverseObjectPropertyDescription] [text] NOT NULL,
CONSTRAINT [PK_UniverseObjectsProperties] PRIMARY KEY CLUSTERED
(
[UniverseObjectPropertyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/ Object: Table [dbo].[UniverseObjectsRendererMaterials] Script Date: 9/19/2025 1:49:09 AM /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UniverseObjectsRendererMaterials](
[MaterialID] [bigint] IDENTITY(1001,100) NOT FOR REPLICATION NOT NULL,
[MaterialType] varchar NOT NULL,
[MaterialOpacity] [float] NOT NULL,
[MaterialMetalness] [float] NOT NULL,
[MaterialTexturePath] [text] NOT NULL,
[MaterialRoughness] [float] NOT NULL,
[MaterialIOR] [float] NOT NULL,
[MaterialEmissive] [float] NOT NULL,
[MaterialColor] varchar NOT NULL,
[MaterialEmissiveMapPath] [text] NOT NULL,
[MaterialUseEmissiveMap] [bit] NOT NULL,
[MaterialSpecularMapPath] [text] NOT NULL,
[MaterialUseSpecularMap] [bit] NOT NULL,
[MaterialWireframeColor] varchar NOT NULL,
[MaterialWireframeLineWidth] [int] NOT NULL,
CONSTRAINT [PK_UniverseObjectsRendererMaterials] PRIMARY KEY CLUSTERED
(
[MaterialID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/ Object: Table [dbo].[UniverseObjectTypes] Script Date: 9/19/2025 1:49:09 AM /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UniverseObjectTypes](
[UniverseObjectTypeID] [bigint] IDENTITY(2200,100) NOT FOR REPLICATION NOT NULL,
[UniverseObjectTableName] varchar NOT NULL,
[UniverseObjectTypeDescription] [text] NOT NULL,
CONSTRAINT [PK_UniverseObjectTypes] PRIMARY KEY CLUSTERED
(
[UniverseObjectTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/ Object: Table [dbo].[Universes] Script Date: 9/19/2025 1:49:09 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Universes](
[UniverseId] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[UniverserName] nvarchar NOT NULL,
CONSTRAINT [PK_UniverseContainers] PRIMARY KEY CLUSTERED
(
[UniverseId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

2

I think that something that is missing in the THREE.JS community, is nonvolatile persistence of our scene objects. Yes, we can serialize the scene to JSON, and store it, I suppose. However, being able to allow someone to store the materials, geometries, quaternions, positions, even tween data in a database allows for extreme modification of any given scene, simply by editing a row in a database with a form ANY of us could create quickly. Change radius? Change one column in one row in a table…It instantly, globally, is rendered with the new variables. I would be happy to pass along any code that shows from an MVC perspective, from database server, to view as JSON, to THREE.JS scene. It all works at runtime, and does not require a repost or further conversations with the server except with AJAX (bullshit, it is not asynchronous javascript and XML for many years) or the fetch API.

3

The proposed structure is not quite optimal and not normalized.

Also, some properties have different names in different objects, but semantically are the same, so there is no need to keep them in different columns. For example, you have Segments, XSegments, YSegments, HeightSegments, RadialSegments, TubularSegnments, CapSegments, ThetaSegments, PhiSegments, BevelSegments, DefaultXSegments, DefaultYSegments. As a result, most of the cells in your table would be empty.

There are some issues, like typos in column names (e.g. Anomoly, Segnments); missing properties (e.g. UUID); rare properties marked as NOT NULL (e.g. wireframe line width, bevel offset and theta start); no support for instanced objects; and so on. The table structure is rather fixed and cannot accommodate easily new Three.js entities with new properties or renamed properties. Modification of table structure of an active project is a big no-no, as it leads to a lot of potential problems.

I’m sharing all this not because your model is bad, most likely it works fine for what you want to do, and covers completely the functionality that you need. This is great, and I like it. My only concern is that the model is not so universal (although it is for the Universe!).

And I have a few questions:

  • Do you store universe’s objects properties at a given time, or the properties are constantly updated? For example, for an asteroid, will the UniverseObjectPositionVectorX be updated regularly to represent the current position?
  • Do you plan to store data for non-orbiting entities (like Oumuamua)?
  • What are your reference frames for position, time, orientation, speed and so on.
2 Likes