A relationship question?

[CharlesWhiteman]

In my Db I have TblPrimaryData. It contains the details of all companies. companies can be categorised in 3 different ways.

1) End User
2) Agent
3) Client

As enquiries are made a link to all three is needed.

I'm wondering whether to have a different table (TblLinks) to keep track of that or, just have three fields in mt TblEnquiries with the ID of each against each enquiry.

Any thoughts/best approach?

 

[CraigDolphin]

It depends.

If a company can only ever be one of the three categories then you'd have one field that stored a foreign key from the Categories table.

If a company can be assigned one or more of thos categories then the correct method is to create an intermediate table between your main table and your Categories table, called CompanyCategories, for example.

In that table you'd store the fk of the company id and the fk of the Categories table. There would be a row for each combination of company id and category id.

This allows you to easily add new categories to your system should the need ever arise, and permits you to easily search for companies that are classified in one or more categories. If you add a date_classified field to the table then you can start tracking the categorization history of a company as well. Which might come in handy in interpreting historical data.

The alternative approach means that any time you need to add or substract a category to your system will require that you add a new field to store that information and then remember to edit any code/forms/queries that refer to the category fields. Not my recommended solution.

Up to you of course.

 

[CharlesWhiteman]

Thanks for your reply. much appriciated. As it goes, writing the question helped me solve it.

On the basis that a company (1 row in the table) can only be one category i have opted to store the relavent ID's in my enquiry table. so a simple solution really.

Many thanks