What is best practice for referencing data rows within stored procedures – via PK, code column or data value?

Suppose you have a table for colours with columns:

  • id = automatically incrementing integer, primary key
  • code = short code reference for the colour, unique
  • colour = human-readable name of colour, unique

Example values might be:

  • 1, BL, Blue
  • 2, GR, Green

Now imagine you have a stored procedure that, at some point, needs to reference this table. Let’s say the business logic says to obtain the colour "Green". To achieve this, you could have any of the following three WHERE clauses:

  • WHERE id = 2
  • WHERE code = GR
  • WHERE colour = Green

Now, if the system is designed such that it is agreed that a code value, once created, never changes, then, in my view, that is the best column to reference because:

  • It is an alternate key
  • It is human-readable for people who maintain the code
  • It will not be impacted when the business decides to change the colour value to ‘Sea Green’

However, if a legacy table lacks such code values, what, in your opinion, is best practice? To reference the id column, or the colour column?

If you reference the id column, the code is not readable unless you then also add comments – you shouldn’t have to comment simple things like this. It sucks figuring out what statements like WHERE id not in (1, 7, 17, 24, 56) mean.

I’m not sure how often, in reality, the id value might change – but consider if you run a script during development to insert new colours but then delete those and insert some more. If your stored procedure references the id values from that last set of colours inserted but when you create your new colours in your next environment you skip the step that inserted the colours which ended up deleted, then the id values won’t match in that next environment. Bad practice, but it can happen – a developer develops their script on a dev instance not thinking that the id values will conflict with production (which, for example, may have had additional colours created manually by the business before your colour creation script runs).

If you reference the colour column, you run the risk that if the business does ask to update the description from ‘Green’ to ‘Sea Green’, that your procedure will begin to fail.

I suppose a further solution is to implement the code column when you need it, if it isn’t there already – probably the best solution?

Best practice for organizing DDL SQL files

I am developing a postgres database with the following approximate number of entities:

  • 60 tables spread across 7 schemas
  • 20 views
  • 20 functions

What’s the best practice for organizing all the DDL SQL?

I currently have a single SQL for the table definitions, another for the views and yet another for the functions. But two of these files have grown to over 1,000 lines each and become unwieldy. That said, there are relationships between tables in different schemas and one file makes these easy to manage.

Would it be better to organize the DDL by schema? Or finer grain still, at the entity level?

I am using JetBrains DataGrip and would appreciate that the solution still enable Intellisense and error checking. The SQL is stored in git.

What would be a good sport game for a beginner/intermediate game dev to make for practice?

I’m interested in making a sport game for practice, but giving it some fun twists sort of like nintendo does, (super strikers charged, mario golf.) I’m planning on using unity, but since I am by no means an expert I’m trying to figure out a sport that will be reasonably easy to create. I’m aware that its not going to be easy, but, for instance I’m not going to make a game like madden, which would have a ton of difficult stuff like play calls which involve predetermined paths and intelligence on both sides, in general way to difficult. What would be a good sport to try a stylized version of?

I’m aware that this is a subjective question, but it isn’t a question where any answer is as good as another, so I think it’s allowed according to the guideline, but correct me if I’m wrong I guess.

As a DM, is telling your players what they conclude a bad practice?

I, a new DM who has run around 10-15 game session so far, am in the process of watching Critical Role Campaign 2 (currently at Episode 23). We all know Matt Mercer and his famous quotes like "You can certainly try" and "How do you wanna do this?". While watching, another phrase he says quite often has come to my attention "You get the sense that…". This has started to annoy me, as it seems that he says this every time, he wants the players to come to a certain conclusion, by telling them what conclusions their characters come to. Any time he does that, I instinctively tell myself: "I never want to do that when I DM", because to me this seems to be breaking one of the most important guidelines in story telling "show, don’t tell!".

As an example in Episode 23 the group was camping outside with Yasha being one of the people who took watch. After the night passed he told them something along the lines of "you get the sense, that anything that was out there during the night didn’t want to come close, because of how scary Yasha looks". This bothers me because, as far as I noticed, the characters had no way of knowing that information. He just told them, that there characters felt that way, which to me seems to be on the same level of telling them that information out of character aka metagaming.

If I think about it more closely, I guess I can see where he is coming from. Not in this example, but usually it appears he is trying to speed up the process of the characters finding out a certain piece of information, as to not drag a scene on for too long, steering them into the right direction. This seems to come at a cost of realism/immersion to me though.

I want to clarify, that I don’t want to diminish Matt Mercer personally or as a DM in a way. He has done so much good for the community and I am learning so much about DMing while watching his Campaign.

But the question I am asking myself is: Would this behavior generally be classified as a bad practice, a DM should avoid doing?

What is the standard practice for animating motion — move character or not move character?

I’ve downloaded a bunch of (free) 3d warriors with animations. I’ve noticed for about 25% of them, the ‘run’ animation physically moves the character forward in the z direction. For the other 75%, the animation just loops with the characters feet moving etc., but does so in place, without changing the character’s physical location.

I could fix this by:
1.) Manually updating the transform in code for this 75%, to physically move the character
2.) Alter the animation by re-recording it, with a positive z value at the end (when I did this it caused the character to shift really far away the rest of the units, probably something to do with local space vs world space I haven’t figured out yet).

But before I go too far down this rabbit hole, I wonder if there is any kind of standard? In the general case, are ‘run’ / ‘walk’ animations supposed to move the character themselves, or is it up to the coder to manually update the transform while the legs move and arms swing in place? Is one approach objectively better than the other, or maybe it depends on the use case? If so, what are the drawbacks of each? I know nothing about animation, so I don’t want to break convention (if there is one).

How to align prefab to a accurate position with the best practice?

I’m making a plumber game, I have a pump prefab, and pipe section prefab, which allows the user to connect the pipe during game, the pipe needs to be connected seamlessly during game, which means pipe prefab needs to be aligned in pixel level, please check below enter image description here

User needs to add more and more pipe to connect the with each other, pipe is fixed length prefab.I’ve got choices,

1.Is the pipe prefab instantiated during game play a good practice? Because it might have many pipe prefabs, if the pipe goes a long way to another position, than there will needs to be so many pipe prefabs, compared to have procedrually generated pipe mesh during runtime, then a long pipe will not have to be cut off to the same small pieces?Which is good way to adpot?

2.If I use the same pipe prefabs to fabricate the piping system, how to accurately connect each other in pixel level, I can make the whole piping system in blender, then cut the system into pieces as my prefabs, then import to Unity as my first choice, this can do pixel level connection, but it seems to have better choice solely solved pixel level connection in Unity?

3.If I do it by generating the mesh during game play, then it could be complex especially it is hard to make elbow pipe mesh during runtime, or could you please advise me of some known library or package can do the work?

SEO best practice for page that is the same for 50 US States

I have a calculator for each US state: for example let’s say you plug in your income and it tells you your state tax rate, which depends on your state.

The calculator has the same UI for ever state, one simple form. The only difference is the page title and H1 tag "PA Tax Calculator" and "Pennsylvania Tax Rates." Of course, your results depend on the state which is generated using JS.

I am trying to decide between:

  • 50 Unique URLs that I send to Google: example.com/calc/?state=PA and example.com/calc/?state=CA, then state-specific titles on Google: "PA Tax Calculator"
  • One URL for indexing (example.com/calc/) that then uses a select form or geolocation to select the correct state. Leave the Google page title generic: "50 State Tax Calculator"

I’d like to be able to put the state title in the Google search title as I think it would feel more personal for vistors, but it seems like if the 50 pages are too similar it’ll just be diluting to have 50 identical pages. This is my first time trying to figure out how to get visitors via SEO.

The best practice of Article / Web 2.0 / Blog submission in off-page SEO?

I am planning for my e-commerce new project. And I am stuck at the best practices of off-page submission for Articles / Web 2.0 / Blogs. I have some questions in my mind which are as follows:

  1. Can I submit the same Article / Post (or content) in different off-page submission sites?
  2. How can I measure that which off-page submission sites are important or useful (like any terms DA, PA or DR).
  3. And How much content submission will effect my domain progress or good link equity?