#🔒 Need some advice related to foreign key and "data visualization" for SQL

11 messages · Page 1 of 1 (latest)

unkempt thistle
#

I'm trying to learn more about databases (very little experience only on toy projects) and rn i'm working for a small company tying to convert their Access db (mostly) written by hand into sql.

I decided to start with some easy tables, to figure out the workflow : companies/contacts

i assumed that company_id is used as a fk for contacts but seems like I was wrong and they actually use company_name as a fk.
this seem a bit awkward, like i get the reason why it's this way but there has to be a better aproach.

rn i'm just trying to redesign the db schema into a format that makes sense. i'd assume going with company_id as fk is the best aproach but I have the following problems:

  1. not sure what tool is going to be used to edit/view the db until i actually create a frontend/automation processes for it
  2. if it's even possible to display the company_name based on company_id on such a tool
full driftBOT
#

@unkempt thistle

Python help channel opened

Remember to:

  • Ask your Python question, not if you can ask or if there's an expert who can help.
  • Show a code sample as text (rather than a screenshot) and the error message, if you've got one.
  • Explain what you expect to happen and what actually happens.

:warning: Do not pip install anything that isn't related to your question, especially if asked to over DMs.

Closes after a period of inactivity, or when you send !close.
finite drift
unkempt thistle
#

I made an erd of how i would expect the final db would look like based on some json schema i was able to get from access

#

Seems like i just didn't pay enough attention while i was making rreferences and renaming columns

nimble crystal
#

an ID (specifically the primary key) is usually what is used as foreign key in other tables. so you're right about that.

#
  1. if it's even possible to display the company_name based on company_id on such a tool
    Not sure what you mean by that. If you got a "company" table, with the columns "id" and "name", then it's easy to get from a companies id to the name in sql. Either with an extra query, or depending on the situation even with a joined query.
unkempt thistle
#

i was thinking more along the lines, rn now they use access to visualize and edit the database

but i think i should just focus on developing the database and that problem is going to be solved when i get to creating an api and some kind of frontend for the db

nimble crystal
#

sure. it's always acceptable to just "do it" and see how it goes, as long as you're willing to spent extra time on fixing mistakes.

full driftBOT
#
Python help channel closed for inactivity

This help channel has been closed. Feel free to create a new post in #1035199133436354600. To maximize your chances of getting a response, check out this guide on asking good questions.