How to create a relation in a database

[koros.komen]

Hi Prows,am creating a farm produce monitoring system using mysql and php.The database contains several tables of different produces that have the similar fields.The admin is supposed to update each table with different values.How will these datas relate.
For example,maize field has Product-id,produce-name,location,date,time and mobile number of the buyer.Each table have the same attributes as that.
Can I create any relationship?
Thanks in advance.

[JorgeM]

Why would you need different tables. It sounds like the information belongs in one table. When you query this table, you would use the WHERE clause to pick specific produce as an example.

Without knowing more information about the tables you have, it would be challenging to advise on options to normalize your data.

[koros.komen]

Yeah I have used a single table for all those produces,so is that enough for me to tell my lecturer that i don't need any relation.Infact i have used a the where statement.
Can I proceed without using any relation?
Thanks

[JorgeM]

You havent provided enough details for me to answer your question, but this info may help you better understand the purpose of creating the relationships.

Say, you have a list of produce and you intend to sell the produce. This can be done with one table, but the data would not be normalized. This table would have data that is repeated on each row. Say like...

oneTable
produceID, produceName, producetype, farmID, farmName, custID, custName, custAddress, InvoiceID, InvoiceDate, etc.., etc...

We can take that one table and normalize the data by breaking up the fields into different tables..

produceTable
produceID, produceName, produceType

farmTable
farmID, farmName

customerTable
custID, custName, custAddress

invoiceTable
InvoiceID, InvoiceDate

salesTable
salesID, invoiceID, produceID, farmID, custID

If you note, your sales table will have one primary key and several foreign keys. Using this approach allows you to easily make changes to the data in the other tables. You can join tables as needed to create reports. You can create other tables, such as linking produce to farms (a farm grows different produce), etc..

[koros.komen]

Thanks JorgeM I think that can be a good idea let try that and i will return back to you

[koros.komen]

Hi JorgeM would you mind if I send you the project then you can analyse it..Am requesting for your email address

[seslie]

Hello, koros.komen,

Why not post what you have so far. The Tables and structures and we'll advise from there.

[JorgeM]

Yes, please post the revalent info. The purpose here is not only to help you, but others that need similar help.

@koros.komen

Glad to see you're on your way with a new structure.

Posting to the forum would be your best bet then you get input from all contributors as opposed to placing the burden on just one person.