Modeling the sky

Data that describes things that move, seen from places that don't. Eight lessons: seven decisions, then the build. You finish with SkySutra's 22 tables in your schema, sample people and places, and a sign-in that gives every new person a row and a home sky.

How Part 1 works

Turnout's data was typed in by people: clubs, events, RSVPs. SkySutra's is mostly the sky itself, and the sky has habits a database has to respect. Stars have catalog numbers older than any app. The Moon is in a different place every hour. "Tonight" means something different in Tampa and in Tokyo. Each lesson takes one of those habits and turns it into a table decision.

Before you start

You need Part 0 done: a workspace with APEX 26.2 and the empty SkySutra app with its two sign-in schemes (Lessons 0.5 and 0.6). If you skipped it, do 0.5 and 0.6 now; nothing in this part needs more.

Lesson 1.1 — Bodies, stars and constellations

Outcome: explain why the Sun, Moon and planets aren't in the star table, and why a star keeps its catalog number next to its id.

The question. Aldebaran and Mars both shine in the sky tonight. Why do they go in different tables?

The decision. Split them by how they move.

A star row carries its address. A body row carries only who it is: a code (MARS), a name, a Vedic name (Mangala), a kind, and the name the Naval Observatory uses for it (for checking our numbers against theirs). Its position lives in other tables, by place and time, or nowhere at all because it's computed on demand.

The lunar nodes, Rahu and Ketu, are rows in SS_BODIES too, of kind NODE. They aren't objects you can see: they're the two points where the Moon's path crosses the Sun's, which is where eclipses happen. SkySutra shows them and says so (Lesson 0.4's "no predictions" rule applies).

Two keys for a star. Every star in the Yale Bright Star Catalog has an HR number, from the Harvard Revised catalog of 1908: Aldebaran is HR 1457. It's the number astronomers and star atlases use, and the number our constellation-line file uses to say which stars to join. So SS_STARS keeps both:

• id, a surrogate key, which every foreign key in SkySutra points at (Turnout Lesson 2.1's rule)
• hr_number, the natural key, unique, used for loading and matching files (Part 2)

Why not make the HR number the primary key? Because the catalog isn't the only source of stars forever. A fainter star added later, from another catalog, has no HR number. A surrogate id never runs out of meaning.

Constellations are two tables. SS_CONSTELLATIONS holds the 88 official ones: abbreviation (TAU), name (Taurus), Vedic alternate (Vrishabha). The stick figure you see on a chart is a separate table, SS_CONSTELLATION_LINES: one row per line, from one star to another. Taurus is 17 lines. Keeping lines as rows means the sky chart in Part 5 draws every figure with one query.

Lesson 1.2 — Nakshatras as data

Outcome: model the 27 nakshatras so the app can show which real star marks each one.

The question. The nakshatras could be a hard-coded list in a PL/SQL package. Why a table?

The decision. Make them data, because the app does more than name them:

The foreign key is the point. The junction star (yogatara) of Rohini isn't the text "Aldebaran", it's row HR 1457 in the star table, with a position the app can compute and draw. One nakshatra, one star; a star can mark at most one.

junction_star_id stays empty until Part 2 loads the catalog: there are no stars to point at yet.

The signs are a small table too. SS_SIGNS holds the 12 signs, 30° each, with English and Vedic names (Aries, Mesha). Lesson 9.4 uses it to show why a Western and a Vedic calendar put the Sun in different signs on the same day.

Lesson 1.3 — Observers and places

Outcome: design people and places so every person has a sky to look at from their first sign-in.

The question. "What's up tonight?" needs a place. What if a new person hasn't told us theirs?

The decision. Give everyone a home from the start, and let them change it.

• SS_USERS is the person: email, display name, theme (Night by default, Lesson 5.1), and home_place_id.
• SS_PLACES is anywhere you observe from: name, latitude, longitude, elevation, time zone, and a darkness rating (the Bortle scale, 1 for a truly dark sky, 9 for a city centre).
• A place with a user_id is yours. A place with no user_id is a shared site anyone can pick: a state park, a dark-sky preserve.
• The first time someone signs in, SkySutra creates their user row and a home place: Wesley Chapel, Florida. Part 7 lets them set their own, from the browser's location.

Who creates the row? Not the pages: a post-authentication procedure, the same idea as Turnout 5.2. APEX proves who someone is; SS_AUTH makes sure SkySutra knows them. Both schemes from Lesson 0.6 call it:

Both end in one function, ss_auth.ensure_user(email, name): find the person by email, or create them with a home place. Emails are stored in lower case, because APEX hands them over in capitals (ASHA.RAO@EXAMPLE.COM) and Google in whatever case the person typed. You build it in Lesson 1.8, once the tables exist.

Lesson 1.4 — Observations and photos

Outcome: model an observing log where every entry has exactly one target and a clear audience.

The question. "Saw Jupiter's moons", "Saw the Pleiades", "Saw the ISS go over". How does one table hold all three?

The decision. One observation, one target, chosen from four kinds:

Exactly one of the four is filled in. That's a rule across four columns, so it's a table-level check constraint:

constraint ss_obs_one_target_ck check (
    nvl2(star_id,1,0) + nvl2(body_id,1,0) + nvl2(constellation_id,1,0) + nvl2(other_target,1,0) = 1)

nvl2(x, 1, 0) is 1 when x has a value and 0 when it doesn't; the four must add up to exactly 1.

Who sees it. Two columns, because they answer different questions:

• visibility: who the owner wants to see it. PRIVATE (only me), SHARED (people with the link), PUBLIC (everyone, once approved).
• status: where it is in moderation. DRAFT, IN_REVIEW, PUBLISHED, RETURNED. It moves only through the workflow in Part 8, never by a page updating the column.

Photos are their own table. One observation can have several photos, so SS_OBSERVATION_PHOTOS has one row each: the image as a BLOB, its MIME type, file name and caption. Deleting an observation deletes its photos (on delete cascade): a photo means nothing without its observation. The other cascades pass the same test: a place's cached rise and set times and ISS passes go with the place, and a person's alert settings go with the person. Observations never cascade, so deleting a place someone logged from fails instead of quietly erasing their log. Lesson 8.6 shows how to move the images to Oracle Cloud Object Storage instead, if you have an account.

Lesson 1.5 — Time the astronomer's way

Outcome: store every time so it means one instant, and know the one place Oracle makes that awkward.

The question. The ISS passes over Wesley Chapel at 6:54 am on October 31. What exactly goes in the column?

The decision. Three rules.

1. Store instants as TIMESTAMP WITH TIME ZONE, in UTC. 2026-10-31 10:54:29 UTC means one moment everywhere. Clock changes, travel and servers in other zones can't shift it.
2. Convert to local time last, on the way to the screen, using the place's time zone: starts_at at time zone p.time_zone. On November 1 the clocks change and the display shifts an hour; the stored value doesn't move.
3. Calculations use Julian dates, a single count of days since 4713 BC. Astronomers use them because subtracting two of them gives days, with no months, leap years or zones in the way. Part 3's ss_time.julian_date turns a timestamp into one.

The awkward place: keys. Oracle won't let a TIMESTAMP WITH TIME ZONE column be part of a primary key or a unique constraint. Try it, and you get:

ORA-02329: column of datatype TIME/TIMESTAMP WITH TIME ZONE cannot be unique or a primary key

This bit SkySutra's first schema run: four tables quietly failed to create. Two ways round it, and SkySutra uses both:

sys_extract_utc turns a timestamp with time zone into a plain UTC timestamp, which Oracle is happy to index. The second way is a function-based unique index, the same trick Turnout used for "one owner per club".

Local dates are different. "Sunrise on October 23 in Wesley Chapel" is about a calendar day at a place, not an instant, so ss_rise_set.local_date is a plain DATE, and the rise and set times in that row are instants with time zones.

Lesson 1.6 — Store or compute?

Outcome: decide, for any number SkySutra shows, whether it belongs in a table.

The question. Aldebaran's altitude over Wesley Chapel is a number. Why isn't there a column for it?

The decision. Store what's true for everyone and for a long time; compute what depends on where and when.

So the rule has a second half: compute by default, store when it's slow or it comes from outside. Stored results are a cache, refreshed on a schedule (Part 10), and every one of those tables has a fetched_at or computed_at column so a page can say how fresh it is (Lesson 4.5).

Lesson 1.7 — Sidereal and tropical

Outcome: store the ayanamsa so the app can work out any date's value, and change systems without changing code.

The question. The ayanamsa is about 24°. Why not a constant in the package?

The decision. It's a dated value, so it's a row:

The value on any date is value_deg + rate × years since epoch. Lesson 0.4 quoted 24°13′39″ for October 23, 2026: that's this row at work, within 22″ of published tables.

Three reasons for a row instead of a constant:

1. It changes with time. A constant is right for one day.
2. Traditions differ. A second system is one more row, and the function takes the system as a parameter, defaulting to Lahiri.
3. It can be corrected. If a better published value comes along, it's an update, not a code release.

The primary key is (system, epoch), so a system can have more than one dated value, and the function uses the latest one.

Lesson 1.8 — Build the schema

Outcome: SkySutra's 22 tables, sample data, and a sign-in that gives every new person a row and a home sky.

The question. We could type all 22 tables from scratch, or let Quick SQL write them. Which?

The decision. Both, as in Turnout 2.9: sketch in Quick SQL to see the shape, then run the hand-finished script, and know exactly what the hand added.

1. Sketch it in Quick SQL

Download skysutra_quicksql.txt from this part's downloads. In SQL Workshop › Utilities › Quick SQL, delete the sample text on the left and paste the file. The SQL appears on the right as you type. Don't run it.

What came out on our run, compared with the finished script:

Quick SQL is fast for the shape; the rules are still ours.

2. Run the finished scripts

Download the three scripts from this part's downloads. In SQL Workshop › SQL Scripts › Upload, upload each one, then Run it, in this order. Each should finish with no errors.

Both scripts are safe to run again. skysutra_schema.sql makes each table and index only if it's missing (create table if not exists), so a second run changes nothing. The seed adds a row only if it isn't there already, matched on its natural key: a body's code, a sign's number, a person's email. It never deletes. Your own people, places, observations and photos stay, and so do the junction stars Part 2 sets on the nakshatras. The four sample observations go in only while the log is empty.

3. Connect sign-in

1. Shared Components › Authentication Schemes, open Oracle APEX Accounts. Under Login Processing, set Post-Authentication Procedure Name to ss_auth.post_auth_apex. Apply Changes.
2. Open Google, set Post-Authentication Procedure Name to ss_auth.post_auth_google. Apply Changes.

Check: in SQL Commands,

select table_name from user_tables where table_name like 'SS\_%' escape '\' order by 1;

returns 22 rows. Then:

select (select count(*) from ss_bodies)      bodies,
       (select count(*) from ss_nakshatras)  nakshatras,
       (select count(*) from ss_users)       users,
       (select count(*) from ss_places)      places,
       (select count(*) from ss_observations) observations
  from dual;

gives 11, 27, 3, 7 and 4. Now sign in to SkySutra as someone new: create one more APEX end user for yourself (Lesson 0.6, step 1), sign in as them, then run

select u.email, u.display_name, p.name home, p.time_zone
  from ss_users u join ss_places p on p.id = u.home_place_id
 order by u.id desc fetch first 1 row only;

Your new person is there, in lower case, at home in Wesley Chapel. If you left the account's first and last name empty, the display name is the part of the email before the @ (gabriela for gabriela@example.com); fill them in and it's the full name. Sign in as asha.rao@example.com and nothing new appears: she was already in the seed.

Checkpoint

After this part, SkySutra has its data model and knows who's signed in. The tables are mostly empty on purpose: the stars arrive in Part 2, the sky math in Part 3, the live data in Part 4.

Test it

1. The 22 tables exist and the counts above match.
2. A new APEX account signs in and gets an SS_USERS row and a Wesley Chapel home.
3. In SQL Commands, try to break a rule, and read the error:

insert into ss_observations (user_id, place_id, observed_at, body_id, other_target)
select id, home_place_id, systimestamp, (select id from ss_bodies where code = 'MOON'), 'Moon and a plane'
  from ss_users where email = 'asha.rao@example.com';

It fails with ORA-02290: check constraint (…SS_OBS_ONE_TARGET_CK) violated: two targets at once.

Files

Exercises

• Add a shared observing site you know, with its own time zone and Bortle rating, as an insert into SS_PLACES with no user_id. Check that the trigger filled geom.
• Add the Raman ayanamsa (Lesson 1.7) as a second row. Look up its value for 2000-01-01 in a published table.
• Write a query that lists each nakshatra with its slice in degrees and minutes (13°20′ is 13.333…°): floor(start_deg) and round(mod(start_deg, 1) * 60).
• Stretch: try creating a table with a TIMESTAMP WITH TIME ZONE primary key, read the ORA-02329, then fix it both ways from Lesson 1.5.

Review questions

Try each one before you open it. The graded quiz is at the end of the page.