Hello there,

This questions is perhaps not in the right spot but it is the closest spot to put it I guess.

Take for example the following entities - Client - Attachment (This is a scanned file such as a copy of a drivers liscence) - Sale - Service Order

The relations are as follows - A Client has a sale - A Client has a Service Order

• A Client has an attachment
• A Sale has an Attachment
• A ServiceOrder has an Attachment

How would one typically setup the table structure that is best suited for LLBLGEN? The way I would normally do it is as follows in option 1.

OPTION 1 (This is the most normalised most extensible way I believe)

Attachment - attachmentId (pk) - blobdata - otherAttachmentFields - etc

Client - clientId (pk) - etc

ClientAttachment (This is just a M:M link table) - clientId (pk) - attachmentId (pk)

Sale - SaleId (pk) - etc

SaleAttachment (This is just a M:M link table) - saleId (pk) - attachmentId (pk)

The advantage of this approach is that the Attachment table structures are not repeated. It is the most extensible method as well. The disadvantages is all the joins necessary which are a pain in the ass in which case I would consider Option 2 probably a better approach.

Would LLBLGEN like this approach?

OPTION 2

Client - clientId (pk) - etc

ClientAttachment - ClientAttachmentId (pk) - clientId - blobdata - etc

Sale - SaleId (pk) - etc

SaleAttachment - saleAttachmentId (pk) - saleId - blobdata - etc

This seems easier because there is less joins as I am ommiting the link tables, but it comes at the expense of maintenance (ie, updating different tables with the same schema).

This is how adventureworks does most things im pretty sure.

OPTION 3

Denormalise

Have the following tables Client - ClientID (pk)

Sale - SaleId (pk)

Attachment - attachmentId (pk) - linkId (Different foreign key depending on the linkType) - LinkType (ClientAttachment, SaleAttachment, ServiceOrderAttachment, etc) - AttachmentBlob (The blob Data)

This is dodgy and I dont like it.

I have attached an image of the layout of Option 1 and Option 2.

Thank you kindly for any advice

Hi John,

The level of normalization depends upon your project needs (performance, end-user usability, programming friendly, etc).

I would opt for Option1. There is not such thing as "a pain in the ass" when you use LLBLGenPro . For Option1 you could construct Target-per-entity Hierarchies:

ClientAttachment --(child of)-> Attachment SaleAttachment --(child of)-> Attachment

That will facilitates your programming a lot.

To achieve that (inheritance) you should change a bit the PK's of the ClientAttachment and _SaleAttachment _tables so the relations should be 1:1. (You have to confirm in your project business rules that two clients or two sales can't have the same attachment):

Attachment - attachmentId (pk) - blobdata - otherAttachmentFields - etc

Client - clientId (pk) - etc

ClientAttachment (This is just a M:M link table) - attachmentId (pk) - clientId

Sale - SaleId (pk) - etc

SaleAttachment (This is just a M:M link table) - attachmentId (pk) - saleId

Thank you Daelmo,

That sounds like it will work well (im still a llblgen noob), I come from a ms-access background and I always disliked fully normalising it like this because it always meant more complicated queries.

The 1:1 thing will suit our business requirements also.

The thing that freaks me out with all this domain driven design stuff everyone seems to be pushing now is that when you get your db architecture wrong its very painful to fix. I'm not sure how those guys deal with it.

I still feel a bit unconfident but this seems good and I will go this way. I hope I don't bugger it up

Thank you John

Good luck with that!

We are here if you need some advise