I’m building a practice version of the celestial bodies database before I dive in and do this for real, just so I can get a feel for how I’m going to structure my response to the question.
The project instructions specifies the following:
Use INT data type for at least two columns that are not primary key or foreign key.
Use NUMERIC data type at least once.
Use TEXT data type at least once.
Use BOOLEAN data type on at least two columns.
I wanted to check – is this meant to apply to the database in general, or to each table?
That instruction applies to all of the database tables, so you could have a Boolean in one table and again in another table to satisfy the requirement.
I’m still doing research on galaxies, and I’ll need to look up data on various moons for the ‘moon’ table, so knowing this will help with planning columns. =)
Like, for the ‘planet’ table, I’m using aphelion (farthest distance from the Sun) and perihelion (closest distance to the Sun) to give an idea of how a planet’s orbit affects its distance from the Sun. Once I got into objects way out on the far edge of the solar system, I realised that having both aphelion and perihelion be in millions of kilometers wasn’t going to work, and that I needed to switch to billions of km.
So I have a few numbers in my notes that I converted to billions that currently have 4 or 5 numbers after the decimal point. Should I round those numbers to fewer digits, or would they be OK as is?
(Just as an example – Earth’s aphelion in billions of km would be 0.152098; its perihelion would be 0.147098; ought I simply round both to three digits?)
I’d been having some issues with NUMERIC sometimes spitting the dummy if I tried to specify the precision, so I’m going to check the postgres documentation to make sure I’m doing it right.
Oh, they absolutely will! You can’t use kilometres for measuring the distance to say, the Andromeda galaxy, because the number would be ridiculously huge. xD; That one, and the stars table, are probably both going to need lightyears instead.
I don’t think SQL accepts numbers with exponents formatted the usual way, though? If I use text, then I can write 3.0e+127, but it’s…usually easier to just use the appropriate units. ^^;
I found we weren’t being judged on the actual data used. I got hung up on that when I realized CodeRoad is assessing the rules, structure etc. They actually tell you to have fun with it I believe. So I used a combination of actual galaxy names and data from Wikipedia and Formula 1 team and driver names. Antonelli being the closest planet and Stroll waaay off in the distance.
This discussion of scientific notation is interesting. I had never found a need. But I looked it up and think it’s cool that you can define a table column as NUMERIC with no precision or scale to store values of any length, then insert those values like this: (3.14159e2) using scientific notation. (Postgres implementation of course)
I’ll admit to being a stickler when it comes to this sort of thing. xD;;;;
Astronomy is one of my areas of interest, although I’m not quite as deeply immersed in it as others I know are. I got rather frustrated at one point during research when I kept turning up planets that Wikipedia didn’t have information fields for, especially where it was information fields that I needed for my planet table. The same thing happened when I was looking up data on galaxies: I found out that some of my galaxies weren’t in the usual catalogues (Messier, NGC, IC, etc), although in that case at least, I found other catalogues that I could reference.
I still need to complete the actual project, but doing a practice version using my own local install of postgresql – and a considerable amount of paper-and-pencil table design! – has very much helped with planning things out. =)
