1

I'm trying to make three tables in phpMyAdmin. The main columns here are named Campaign, Program and Asset. Under Campaign, there are different kinds of Programs. And under each Program, there are Assets with corresponding columns. Here is the rough sketch of what I'm trying to build right now.

  • Campaign 1
    • Campaign Description
    • Program 1
      • Program Description
      • Asset 1
        • Asset Type
        • Asset Description
        • Region
        • Supporting Asset
        • Status
        • Release Date
        • External Link
        • Source File Link
        • Contact Person
        • Target Audience
        • Color
      • Asset 2
        • Asset Type
        • Asset Description
        • Region
        • Supporting Asset
        • Status
        • Release Date
        • External Link
        • Source File Link
        • Contact Person
        • Target Audience
        • Color

How can i construct my tables like these interconnecting with each other? How do i do querying? Thanks in advance! :)

2

While there are different approaches to manage your problem, the simplest way to do it is following the most conventional relational and normalised way, which is separating Campaigns, Programs and Assets as different tables. You can specify relationships between tables like "has" or "belongs to" with foreign keys that references other table rows.

You can read an introduction about the relational model here: http://en.wikipedia.org/wiki/Relational_model

When querying complete information, you have to use the JOIN clause ( http://en.wikipedia.org/wiki/Join_(SQL) ) to merge results from different tables, for example:

SELECT * FROM campaign 
JOIN program ON campaign.id = program.campaign_id 
JOIN asset ON program.id = asset.program_id

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.