Not sure if this is the best place to post this question but thought someone hear might now a bit more then me about MySQL. we are currently setting up some table to store are user data and im looking into how we can link this two tables together so that is we update the data in a row of one of the tables it will update the data in are other table respectively based of the PK.
so i have the following table that in the first photo i want to be set up as are master and in the second photo is are slave table so when we make a row in the slave it will have a matching userName and assetName column to the parent and im wanting to use that to then link the rows so if we query change something in the master say we change are assetName it would make the same change to are slave. so im not sure if what im looking to achieve is possible from what I've read it seems like it should be using foreign keys but im vary new to this so i could be mistaken.
Whether there's a way to do what you've asked in MySQL I don't know.
What you should do is set up a third table that stores asset information.
This table should have a primary key and an asset name column.
Then the first two tables should be linked to the asset info's id column. The ID is stable, and the name is therefore free to be updated. Whenever you're querying from the other two tables, you just have to join in the current asset name from your asset info table.
Do you have control over the tables all around?
You shouldn't have the columns userName and assetName be in two places. Instead, you can create a primary key and link the tables via foreign key. Then you can simply do a join to get the proper userName and assetName. Otherwise, you will unnecessarily have to manage both of those in a situation like you stated above.
Foreign keys are the way to link tables together in this manner and maintains data integrity. You can also set different behaviors for when foreign key values change as in On UPDATE and On DELETE actions. The options are
- No action
- Cascade
- Set Null
- Restrict
4 and 1 do the same thing, it prevents foreign key values to be altered. 2 will update all entries that refers to the foreign key that is altered (there is overhead involved here). 3 will set the FK value to null.
ok we currently do have are two tables linked but are running into the problem where when we make a change on are parent the chilled doesn't update so i changed are option to onUpdate and that seems to have fix are data righting back and forth and just some more testing to make sure it is fully working. I am wondering still about what problems would come from me only having the two tables and linking them together im not sure i understand why we need a 3rd table was this just to help with queries and making sure the data we want is pulled from both tables with the use of just an ID?
Dude! Grammar! Punctuation!
These matter in professional contexts, and are vital to non-native-speakers being able to participate. And are enough like gobbledygook for people like me to simply skip.
sorry i have dyslexia i try my best.
is there a way we can link tables together without needing the column to be a PK as we want are isVisible column to be linked but don't want it as a PK.
I am still unsure that you even need two tables for your scenario.
you want them to be the same so why are there two tables at all?
this strengthens the idea you don't need more than one table.
If you are wanting a parent child relationship between assets you can link a foreign key to a primary key in the same table to achieve this easily.
The two tables is because when looking into SQL tables i found a lot saying not to make lists in one column and i need each asset to be able to be part of multiple groups. so I broke the groups out into a new table there could defiantly be a better way to go about this that im not aware of im vary new to SQL.
So we managed to get everything linked it doesn't wright from the child to the parent when you make a change but it does from parent to child which works for how we will be using this. Ill be doing some more research into how I can maybe make this one table instead of two as it seems you are suggesting there should be a way so ill keep looking. Thanks for the help sorry for the bad spelling, grammar, and general comprehension of my messages i do try my best.
I would strongly suggest doing some basic research on database normalization and, before you do anything else in your database, stop and plan your DB structure. A few hours of research and planning in the short term will save you days in the future untangling things and regretting past decisions.
In general, nothing that might need to change down the road should be used as a primary key directly, (i.e. an asset name is not suitable as a stable identifier if it might be changed) and, you're correct, you should not be storing lists in a single row of a single column. You should always use additional table(s), as needed, to store multi-valued sets.