Multiple fields with same name
-
Hi all
Small problem that I hope someone could shed some light on for me. Being very new to SQL I am not sure how best to approach this.
I have a collection of tables that are dependant on each other.
Table 1 = Minerals
fields = Min_ID, Min_Name, Image_URl
Table 2 = Simple reactions
Fields = SReac_ID, SReac_Name, Min_ID, Min_ID, Min_ID, Min-ID
My problem comes with Table 2, being unable to name the fields with the same name, would I have to create 4 x Table 1 to create this, or is there another way?
Regards
Steve
-
Do you mean that Table2 has 4 columns that refer to different rows (Min_IDs) in Table1? If so, then create the four columns with different names and you're set.
-
How about using the correct design and creating a third table?
-
I agree with Remi,
Sounds like a join table is the proper solution.
Minerals (Min_ID, Min_Name, Image_URl)
Reactions (SReac_ID, SReac_Name)
MineralReactions (SReac_ID,Min-ID)
Wayne
-
Without knowing the requirements behind this design we cannot say it is incorrect. For instance, if a Reactions should always have 4 mineral ids, then the original design would probably be better.
-
oooo... This might get philosophical, although that is not my intention.
I like to approach design from the "Normalize until it hurts, denormalize until it works" perspective. I forget who on this board uses that for their signature, but I have used it frequently in conversations with colleagues.
IMO, writing queries against the join table results in fewer errors and fewer maintenance issues than trying to remember specific column names for simulating an array. Anytime I see the word "always", as in "should always have 4 mineral IDs", I question the requirement. Join tables allow for easier future maintenance in the case where there is additional data associated with the reaction/mineral combination. For example, volume of the specific mineral for a particular reaction seems like something we should plan for.
I freely admit that I am speculating as well and do not know the full system requirements either.
Wayne
-
I am not in that camp.
I am in the "There is no such thing as denormalization"-camp.
Anyway, my primary concern when designing is data integrity. If there is a constraint in the requirement that says that a foo has 4 bars, then I make sure the design includes that constraint. I am not saying I believe this case has that kind of a business rule (nor do I say it does not), just that if it does that would be my primary concern. And note that there is nothing that says that the design would not be fully normalized. Having four columns that store similar data is not the same as an array. If each column has some specific meaning, and if there is a constraint that all four must always exist, then this is probably the design I would use.
-
Please excuse me if I did not articulate the problem efficiantly (I am still in the SQL paddling pool, with armbands on!)
As a learning project, I am trying to create a GUI that queries a DB to return the results of a number of reactions.
Typical example
Silicates(Mineral) + Hydrocarbons(Mineral) = Carbon Polymers(Simple Reaction)
Carbon Polymers(SReac) + Crystallite Alloy(SReac) = Cyrstalline Carbonide(Complex Reaction)
Crystalline Carbonide(CReac) +Sylramic Fibers(CReac) = Crystalline Carbonide Armor Plate(Component)
I would like the query to return all the reactions and minerals for a selected component
Typically Simple reactions only combine 2 minerals, but Complex reactions can contain up to 4 Simple reaction.
I dont know much about join tables, but am going to look up all the info I can on them right away.
Thanks for the replys
Steve
-
If the column names are the same in multiple tables you have two choices - aliasing or object qualification. Either one will do. We have some similar issues with 3rd party vendor software. Excuse the 'old' join syntax used in the example.
create table table1 (col1 int, col2 int)
create table table2 (col1 int, col2 int)
select t2.col1
from table1 t1, table2 t2
where t1.col1 = t2.col2
--or
select table2.col1
from table1, table2
where table1.col1 = table2.col2
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply