Hi, this is my first time posting here. I searched for a couple of hours but didn't find the answer I needed, so here goes:

I've attached a very basic relationship diagram that I slapped together, just for the sake of hopefully making things easier to explain. This database is meant to store market research survey scores for our client's products. I've got two main problems, both dealing with circular relationships, but both slightly different.

Problem 1:
- In order to make future maintenance easier I've stored all of the product attributes in the "Attributes" table and am linking them to the "Products" table via an intermediate table (which will store each product's score in each particular attribute) in order to avoid a many-to-many relationship.
- I've been instructed that each client should only be able to view a selected sub-set of all existing attributes that relates to their products, and that we should be able to add new attributes in the future for particular clients only. For that reason I need to also have an intermediate table between "Clients" and "Attributes" to store the permissions/customised viewing profile for each client.

I was taught that circular relationships are to be avoided at all costs, but does this kind of situation occur frequently in real applications? I can't figure out a way to achieve this without creating a circular relationship...


Problem 2:
- Each client has a number of brands that belong to them
- Each brand contains a number of products
- Each client can undertake any number of market research surveys ("Projects"), in which a number of products (both theirs and competitors) are tested. Because these projects can contain products from multiple clients and multiple brands, but are undertaken by only one client in particular, it is related to both "Clients" and "Products".

This again, creates a circular relationship. Is this unavoidable? The entire reason I have a Projects table is because we need to also store details of the surveys that can have an impact on the products' scores, and keeping these details stored in the Products tables would cause redundancy.

I'm aware that this diagram, no matter how simple, must look ridiculously stupid and ugly to the much more experienced database administrators on these forums. Any advice at all is appreciated.

i shouldn't be too concerned about "circular" relationships -- i don't even know what that means, and i've been modelling for a few (!) years

in your diagram you have projectid as a FK in products -- this means each product can belong to at most 1 project

I take "circular" relationships to refer to the fact that you can follow relationships like this:

Clients -> Brands -> Products -> Projects -> Clients

There is nothing wrong with that though. I think what you have been warned against is redundant relationships that can be omitted because they are inferred by others - like this, if you can follow the sketchy pseudo-ERD:
i.e. if each Product is associated with exactly one Brand and each Brand is associated with exactly one Client then we don't need to state also that each Product is associated with exactly one Client. Your case isn't like that.

BTW Rudy, isn't it the middle of the night where you are?

nope, 7 am

First of all, thanks heaps for the helpful responses.

Yeah, you're exactly right. It's been a few years since I did database development & design so I couldn't remember the proper term for it (doesn't help that I lent my textbooks to someone and never got them back). I might have been getting mixed up with "circular references".

That is what's happening here though, isn't it? I've got both of the following relationships:

Client->Brand->Product
Client->Project->Product

There are definitely two paths to follow to get from Client to Product. I need to change that somehow, but I can't figure out how for the life of me, since I need to know both which products/brands belong to each client, and also link the details of each project to the client that ordered the project. If you think it's not a big deal as long as Product is not directly related to Client though, then I guess I'll stick with what I've got.

That's true, I guess I overlooked that...
Thanks!

That is the middle of the night as far as I'm concerned

It's not a problem because none of the relationships is redundant - i.e. if you remove any one of them, you lose important information. A relationship is only redundant if you can drop it without loss of information.

What you have is actually quite common. You will (I imagine) want to ensure that the same Client is associated with the Brand and Project for each Product. This can be done in one of the following ways:

• Create only one ClientID column in your Products table and include it in both foreign keys.
• Create both a BranchClientID and a ProjectClientID column in your Products table and add a constraint: CHECK (BranchClientID = ProjectClienttID)

Actually, a client can initiate a project which includes both their own product and a competitor's product from a brand that doesn't belong to them, so I wouldn't be able to do it that way, but thanks for the information. As you can tell I'm not experienced at creating commercial databases yet, so every bit of advice is valuable.

So your circular relationship non-problem is an even bigger non-problem than we thought! If a product can belong to one client's project and another client's brand then for sure you must record 2 relationships - otherwise your model would only support the cases where brand client and project client are the same.

The next problem is convincing my supervisor.

Thanks heaps.