Data Structure Help

[rabadaba]

I am new to php - MySQL and need help in defining a proper robust structure for data to be shred between two tables.

 

The first table is the 'category' table, which contains 'id',  'Element X', 'Element Y' etc.  I am using the 'id' as the logical category because it ties in well.

 

The second table is the 'content' table with its own 'id' (not related to the category table) 'ElementA' ElementB' etc.

 

 

My question -  I need to link the two tables.  Should I:

 

1. Add a field to the 'category' table to tie to one of the Elements in the 'content' table

 

or

 

2.  Add a field to the 'content' table to tie each row to its parent 'category'  'id'?

 

 

What makes the most efficient use of coding and structure and why?

[quiettech]

Relationship between tables is tied to the meaning of the data. It's hard to give you an answer without you being more specific.

 

What is exactly "content" and "category"? I don't know if a category can have 0 or more contents or contents can belong to 0 or more categories. Once you establish those relationship rules, the rest falls naturally.

 

For instance, if a content can belong to only 1 category, the relationship could be established by adding a field to the content table - say, named "catid" - and create a foreign key relationship to category through that field.

 

If one content could 0 or more categories, you would do this with the help of a 3rd intermediary table...

 

If you can be more specific, it will help.

[rabadaba]

My webiste is quite simple.

 

I am using table names - so there are many other fields but that it not my question.

 

All 'content' items can only belong to only one category.

 

Thus I take the best solution is to add  a 'cat_id' field to the content table... 

 

 

[quiettech]

In that case yes. You create a "catid" field (can be any other name thet suits you best) and make it the exact same type as the ID field of the category table, assuming that's also the category table primary index. It won't hurt making that new field an index.

 

That should be enough. However you should also create a foreign key relationship to the category table. This will ensure your data integrity; i.e. you won't be able to delete a category, for instance, if there are items in the content table pointing to that category.

[rabadaba]

Thanks for your input.  It is very useful for a newbie like me.

 

 

On your last point about deleting a category...  I may have a need to add a category in the future and the new category will probably not be at the bottom.  Thus I am planning on writing a query (when the time comes) to shift the category table to allow for the insert of the new category item in the proper ('id') order.  I will also have to change all the affected 'cat_id's in the content table to match.

 

Any hints on how to set up properly for such an event?

[quiettech]

Hmm... You should avoid using the id field for that. Consider it as a identifier only. It just identifies each category uniquely.

 

If you need categories to relate to each other in that way, you add a field to the category table where you express that relationship. For instance, a field named "order". Any changes you need to make, you make to this field. As such, "order" is expressed as a property of a category.

[rabadaba]

If I don't use the id field for order.... how do I control the order my categories are pulled from the database.  I need some field that has a ordered order.

 

What is the proper structure to follow to allow me to oder the category table?

 

 

[aschk]

Why would you order by ID, surely you could order alphabetically? Users like alpha lists, not ordered by some unknown number, making it impossible to find category because A might be in between F and G