1
\$\begingroup\$

I am working on moving a save system from loose files to a (spatial sqlite) database.

The user places objects from a library in a 3d simulation. Scenarios are "what-ifs", what if we place 5 wind turbines over here, what if we placed 10 racks of solar panels instead etc. They are mutually exclusive within a "group".

Items are prototype objects that can be instantiated by the user. Wind turbines and solar panel racks are specializations of items with extra data.

This is the first time I've done SQL outside of trivial school projects so please don't hold back on pointing out best practices / tips.

Do my FKs make sense?

Is it proper in form?

Thanks in advance.

interactive version: https://dbdiagram.io/d/5e04aa5eedf08a25543f6bc5 you can export SQL from there if that is easier to read.

pseudo sql below.

//a mutually exclusive group of scenarios
Table group
{
   group_id int [pk, increment]
   name text [unique, not null]
   active_scenario_id int [ref: - scenario.scenario_id]
   is_active bool
}

//a user created arrangment of item instances
Table scenario 
{
  scenario_id int [pk, increment]
  group_id int [ref: > group.group_id]
  name text [not null]
}

//an item is a prototype. an object that can be repeated many times.
//the name identifies a prefab in the application
Table item
{
  item_id int [pk, increment]
  name text [unique, not null]
}

//an instance of an item prototype in the world
Table instance
{
  instance_id int [pk, increment]
  scenario_id int [ref: < scenario.scenario_id]
  item_id int [ref: - item.item_id]
  position PointZ [not null]
  rotationX float [default: 0]
  rotationY float [default: 0]
  rotationZ float [default: 0]
  scaleX float [default: 1]
  scaleY float [default: 1]
  scaleZ float [default: 1]
  active_date datetime [null]
  inactive_date datetime [null]
}

//a LineString created by the user.
Table user_path
{
  instance_id int [pk, increment, ref: - instance.instance_id]
  win3d_style_id int [ref: < win3d_style.win3d_style_id]
  path LineStringZ
  viewPath LineStringZ
  float duration [note: 'duration if this path is an animation']
}

//a polygon created by the user
Table user_polygon
{
  instance_id int [pk, increment, ref: - instance.instance_id]
  name text [not null]
  geometry PolygonZ
  win3d_sytle_id int [ref: < win3d_style.win3d_style_id]
}

//a wind turbine is a kind of item with extra data
Table wind_turbine
{
  item_id int [pk, increment, ref: - item.item_id]
  rotor_diameter float
  shaft_height float
  shaft_base_diameter float
  shaft_neck_diameter float
  min_wind_speed float [note: 'wind speed in m/s at which the blades starts spinning']
  max_wind_speed float [note: 'wind speed in m/s at which the blades reaches peak rotations per minute']
  off_speed float [note: 'wind speed in m/s at which the blades is stopped for safety']
  min_rotations_per_minute float [note: 'rpm at which the blades spin when the wind speed equals min_wind_speed']
  max_rotations_per_minute float [note: 'rpm at which the blades spins when the wind speed equals max_wind_speed']
  watt_min long [note: 'Watt produced at the min_wind_speed']
  watt_max long [note: 'Watt produced at the max_wind_speed']
}

Enum solar_panel_rack_kind
{
  poles
  praxiz
}

Enum solar_panel_facing
{
  south
  east_west
}
//a solar panel rack is a kind of item with extra data.
//a solar panel rack is an arrangement of solar panels
Table solar_panel_rack
{
  item_id int [pk, increment, ref: - item.item_id]
  solar_panel_id int [ref: - solar_panel.solar_panel_id]
  num_columns int
  num_rows int
  column_gap int [note: 'centimeters between each column']
  row_gap int [note: 'centimeters between each row']
  kind solar_panel_rack_kind
  facing solar_panel_facing
}

Enum solar_panel_kind{
  polycrystalline
  monocrystalline
  bifacial
}

Table solar_panel
{
  solar_panel_id int [pk, increment]
  width int [note: 'centimeters']
  height int [note: 'centimeters']
  watt_peak long
  kind solar_panel_kind
}

Table win3d_style
{
  win3d_style_id int [pk, increment]
  primary_color uint32 [default: 4294967295, note: 'default is opaque white']
  secondary_color uint32 [default: 4294967295, note: 'default is opaque white']
}

Table point_of_view
{
  point_of_view_id int [pk, increment]
  scenario_id int [ref: < scenario.scenario_id]
  name text
  position PointZ [not null]
  rotationX float [default: 0]
  rotationY float [default: 0]
  rotationZ float [default: 0]
  field_of_view float [default: 30]
  thumbnail blob [note:'jpg encoded image']
}

image of schema

\$\endgroup\$
1
\$\begingroup\$

few comments:

  • avoid data type float, unless you don't care about not accurate rounding.
  • Solar_panel_rack, solar_panel and wind_turbine I would change structure:merge solar panel with solar_panel_rack if possible also would probably join wind turbine with solar panel_rack. It could potencialy have columsn like: ItemId, ItemTypeId(wind turbine or solar), ItemName, other characteristics. Your current architecture would complicate future queries.
  • remove item table as it is just smaller version of wind turbine and solar panel
\$\endgroup\$
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.