I have two UI forms that show data entry fields for 2 entities which correspond to 2 db tables. Think of Customer and Supplier.

I would like to track files that are associated with each entity uploaded by the user in an Uploads table. The Customer form has an upload button so does the Supplier form. When the button on the Customer is pushed it shows uploads for that specific customer. Nothing else.

All the information per upload is identical except the idea that I have to know what entity a given file is supposed to belong. One row in the Uploads may belong to the Customer form, another row may belong to the Supplier form.

In the past I have added "generic" ID field in the Uploads table along w an identifier that points to the related table. Something like

PK: 1 FileName:Whatever BelongsTo: Customer BelongsToId: 300 PK: 2 FileName:Another BelongsTo: Supplier BelongsToId: 12

Will LLBLGen become utterly confused if I simply relate the Customer and Supplier ID's in the database relations to the same generic "BelongsToID" in the Uploads table?

Is there a way around this potential code smell by defining subtypes in some fashion that use BelongsTo as a Discriminator so I would effectively try to create a SubType called CustomerUploads and one called SupplierUploads and then have LLBLgen sort it all out based on the BelongsTo discriminator? And assuming thats even possible, can I then wire up the CustomerUploads collection to the Customer entity ... or am I completely off track with this entire concept?

I see it a little bit differently.

For UploadedFiles, this should stay intact, one table, no hierarchy.

Customer and Suppliers, this may qualify to a hierarchy having a parent table say: Contact

So Contact has 2 SubTypes: Customer & Supplier.

UploadedFiles has FK that points to the Contact table.

Thanks Walaa - I think unwittingly I used an example that has a built in suggested answer in the super type. The example I wrote was for ease of understanding. I didnt go into the actual domain discussion because the post would have become way too long and you would have had questions about the meaning of certain entities,,, hence it seemed using something familiar like suppliers and customers makes it easy to understand. Is the safest approach really to look for a generic supertype ? Is there no way to use a structure as described in the question.

A supertype of supplier and customer allows you to define a relationship between supertype and uploads table, and you can then use a type filter at runtime to get the customers or suppliers with respect to that relationship.

The way you described it, would mean the FK has to check in the table which is named in the field 'BelongsTo', which isn't going to work in practice.

You can also do it differently: you can model the relationship customer - upload in a different table as supplier - upload. This removes the 'belongs to' fk from upload and places it in the two tables modeling the association. So this gives: CustomerUpload, with two fields: CustomerID and UploadID, both form the PK. CustomerID points to Customer, UploadID points to the actual upload table. SuppliedUpload, same thing, but now you have a SupplierID and UploadID.

All uploads are still in the Upload table, however you now have the relationship modeled away. No supertypes needed in this case. As you access uploads separately anyway, this is not a problem for you. The only drawback this has is that if you just want to show 'uploads' and 'who they belong to', you have to create more complex sql and have to interpret the rows: you then have to do :

SELECT U.*, CASE CU.CustomerID IS NULL THEN 0 ELSE 1 END AS BelongsToCustomer, CASE SU.SupplierID IS NULL THEN 0 ELSE 1 END AS BelongsToSupplier FROM Upload U LEFT JOIN CustomerUploads CU ON U.UploadID = CU.UploadID LEFT JOIN SupplierUploads SU ON U.UploadID = SU.UploadID

e.g. inside a view for example. You then can consume the belongs to fields I created in your UI. You can also do it differently and fetch customer and supplier related to an upload with prefetch paths, it's up to you.