Database setup for a a webapp.

[Project37]

I need a help to getting started with something I am building. I just want to make sure I am going in the right direction.

 

So I am trying to build a webapp for work. My programming skills are at a beginner level, I just started learning PHP for this is project, and before that it was just brief stints in HTML/CSS. I usd codecademy to learn the PHP i know now, and some youtube tutorials. So, where I am looking for help. I have not learned databases at all and will be doing the MySQL tuts on youtube from thenewboston.

 

My end goals is a web app that users can submit a form to signal they are running low on material and someone will check a page to  see what and where the material is needed. So one workbench, along the lines of(cell.company.com/bench.php) would set what they are part they are building and what the status of the material is, in stock,low or out/needed urgently. A page for the person responsible for the stocking of material world display any workbenches that are low or out of material, what the material is and other charicteristics. (cell.company.com/materialmover.php).

 

My thought is each workbench is a table the builder can update through a form, such as what part they are running and the status of the material, the table would be altered by the page they would load. The table collecting that data would only be as long as the amount of benches, and it would have colunms for a part_ID and a status bool. There would be a condintion that if the status bool is TRUE, and the user submits the request for more material, it would display to the material mover and also email them.

 

So I identify 4 tables, one for benches that can be altered with info from the part table. and a material table that display the material info since materials can make many parts. Also a table that logs what and when the request was submited for data tracking purposes, such as how often and how long it had taken to fufill the request.

 

I guess my questions I have are:

Is this the right thought process?

Is altering a table over and over a good way to do this, or should I let the table fill up with requests and track from that table?

For a relationship between tables be along the lines of  "SELECT * FROM partNums, material_info WHERE partNum_ID = 1" to select the correct material for a part?

 

 

As I said earliar, I will be studing MySQL more, but just wanted to see if my thought process is in the right direction.

 

IF I am not clear enough, please ask. I spent a while writing and rewriting this, and confused myself, so it may not be as clear as I am hoping it is.

[Project37]

Another thing I am thinking about is OOP way to do this. So a parent class workbench that update, read, create and delete datafrom thje database, also have a funtion to check the status. ButI am unsure how a child class will read the proper row of data in the bench table. Wouild you have a condition to look for a field that has the same name as the child class. So bench1 would have  condition to read the row that == its own name. Or sometihng along those lines. 

[Ciccioo]

i'm not a master in english and i'm tired, so please confirm that i got it right

 

you're thinking of 4 tables

- parts table: describes which things can be "crafted" in the workbenches, and which materials are needed for them

- benches table: a list of the workbenches and the parts that the bench can produce

- materials table: a list of the raw materials that are then used by benches to build parts

- log table: to record all the transactions

 

anyway, since you're using a relational database and you're in the phase of thinking of a data organization, you could use writing down a E/R model to have things more clear

 

and what table do you say you would "alter" when you say

Is altering a table over and over a good way to do this, or should I let the table fill up with requests and track from that table?

 

before moving on you should take a deeper look into how relational databases work, the query that you posted is not very good and in your second post i see you still have doubts about how to establish relationships between tables

i would link a good guide, but i don't know any, sorry, there is a pinned topic for programming stuff tho, maybe you will find something there

 

and yep, OOP approach = good

[Project37]

Thanks for the reply, the table I would alter is the bench table, to update what part is running on it.

 

Yeah, I have a lot to learn and am going to be studying databases/MySQL. That will help treamendously, sinceI realy do not know how to use get data in or out of a DB yet.

 

Sounds like my approach isn't to far off.

 

So along with learning how to communicate with a DB, I will need to also learn the structure of DBs.