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
4

“As a result, most of the cells in your table would be empty.”

-It is very common to have a database row with not all fields populated. In addition, an object may have hundreds of properties some of which are actually objects themselves that have values that are not populated. The different segment arguments are not mine. They are all different segment properties for different geometries in THREE.JS. What I did, was condense the table count from one table per geometry type with unpopulated properties to ONE table with the ability to hold and presist the data for any possible geometry with all possible properties taken into account. Image that one database row, and all of the columns, as an object. A geometry object. One class that can create any and all possible geometries…

  • 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?

No. The spherical coordinates are an initial vector. Along with when it was at that vector. After that orbital mechanics can calculate the spherical coordinate at another given time. Storing a position and quaternion vector 60 times a second in a database is pointless. The database is there to persist data used to calculate realtime at runtime in whatever orbital modeling tools chosen.

]Do you plan to store data for non-orbiting entities (like Oumuamua)?

That ability is already there. You can store a to scale proton. Or a GLB representation of a cell phone on the planet surface. Just leave out the keplerian variables, and no orbit calculation will be attempted due to lack of data. Likewise, without a vector3 the object will not be rendered because I do not know where to instantiate the mesh.

What are your reference frames for position, time, orientation, speed and so on.

Most people use a now time of milliseconds since the Epoch 1970. If by reference frames you mean runtime rendering,. as fast as the damn machine can calculate as many objects that are in the table and render them in as much detail as possible, until I crash the machine./ browser/ renderer. The database is unlikely to be even a blip on CPU/DISK storing the data required to render for millions of objects with hundreds of concurrent users requesting object data for a rendering engine…

5

Schema on the way…

6

CREATE TABLE [dbo].[UniverseObjectGeometries](
[GeometryID] [int] NOT NULL,
[UniverseObjectID] [bigint] NOT NULL,
[GeometryType] varchar NOT NULL,
[GeometryComplexity] [int] NULL,
[GeometryRadius] [float] NULL,
[GeometryTube] [float] NULL,
[GeometryArc] [int] NULL,
[GeometryDetail] [int] NULL,
[GeometryLength] [int] NULL,
[GeometryHeight] [int] NULL,
[GeometryRadiusTop] [float] NULL,
[GeometryRadiusBottom] [float] NULL,
[GeometrySteps] [int] NULL,
[GeometryDepth] [int] NULL,
[GeometryInnerRadius] [float] NULL,
[GeometryOuterRadius] [float] NULL,
[GeometryThresholdAngle] [float] NULL,
[GeometrySegments] [int] NULL,
[GeometryXSegments] [int] NULL,
[GeometryYSegments] [int] NULL,
[GeometryHeightSegments] [int] NULL,
[GeometryRadialSegments] [int] NULL,
[GeometryTubularSegnments] [int] NULL,
[GeometryCapSegments] [int] NULL,
[GeometryThetaSegments] [int] NULL,
[GeometryPhiSegments] [int] NULL,
[GeometryBevelSegments] [int] NULL,
[GeometryBevelSize] [int] NULL,
[GeometryBevelOffset] [int] NULL,
[GeometryBevelThickness] [int] NULL,
[GeometryPhiStart] [float] NULL,
[GeometryPhiEnd] [float] NULL,
[GeometryThetaStart] [float] NULL,
[GeometryThetaEnd] [float] 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