Hello there,

I was wondering if anyone could give me some advice on what they think is the best option for modeling the situation below;

Take 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? 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 link table)
- clientId
- attachmentId (pk)

Sale
- SaleId (pk)
- etc

SaleAttachment (This is just a link table)
- saleId
- 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.

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).

In this instance the maintenance won't be a huge problem but in similarly modelled data structures it might be.


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.

The database will be for Sql Server using LLBLGEN Pro. This is probably irrelevant to the question but I believe the implementation of option 1 will not be a huge drama.

Thank you kindly for reading my message and I very much will appreciate any thoughts.

i like option 2 because it's simpler

ask yourself if a given attachment can ever belong to more than one client or sale

if the answer is yes, go with option 1

Thank you r937.

An attachment can only ever belong to one client or sale. Although technically a sale belongs to a client, so the attachment belongs to the client also (but i think this is irrelevant).

I agree that option 2 is simpler but what kind of bothers me a little with it is as follows;
We will most likly have other attachments types, for example, service order, user and contractor attachments (and quiet possible others I havent thought of yet)....so these tables will grow.

So say we end up with 5 or 6 different attachment types and then at some stage need to add/remove fields I'd have to make that change 5+ times instead of just once.

I have the same dilema with things such as say address entities on client/vendor/staff entities...(and a few other scenenarios).....

In the past (using MSAccess) I have put clients, vendors and staff all in one table so this has not been a huge issue for me and that all worked quiet well as it would only be the one related attachment table anyway.

I believe option 1 allows me to setup inheritance for OO programer guys alot easier, but i'm still a little bit noobie in that area.

I guess now I don't know what to ask other than does anyone else have any conflicting advice on that or knowledge on the OO impedence mismatch problem?

Thanks heaps for your thoughts r937

The Entities of ER modeling and the Objects of OOP are so closely related in my mind that the difference amounts to arguing about the shapes used for splitting hairs. What works for one ought to work perfectly naturally for the other.

If an attachment belongs to a sale, and a sale belongs to a client, then the attachment does not belong (directly) to the client. This might seem subtle at first, but understanding an object model hierarchy is important. For a real world example, an assembler has a gender and an automobile has one or more assemblers, but the automobile has no gender.

Modeling is something of an art. In your MS-Access example, I think that you first modeled people, then added attributes or relationships that allowed you to distinguish the types of people as clients, vendors, staff, etc. This is good design from both the OOP and the ER point of view.

From my perspective, if you see a mismatch between ER and OOP, then the problem lies in how you are looking at things, not in the things themselves. I see no mismatch.

-PatP

I am still coming to grips with fully OOP design principles. If they were so similar then why would people go to all the effort of mapping them to a relational db? Well I think I know the answer but this is probably a different thread that im sure has been had before...

In regards to my access project client/vendor/staff problem, you are correct, for the most part they all shared the same properties and performed the same methods, and were related to the same entities.....it made sense to stick them in the same table. In this instance I called them a "Cardfile" just meaning a company/individual we dealt with and added 3 bool fields. It worked very well for me.

From an OO point of view however, I probably would have had a super class and 3 child classes. The super class having all the common properties and methods and the 3 child classes having those which are specific to themselves.....perhaps this would be overkill and perhaps is similar to the null fields arguements...(In DB's I am quiet content with null fields as long as theres not too many of them and if it cuts down on enough labour). Mind you I am out of my depth to be arguing about OO design.

My main reason behind supporting entity driven design is that database schema's and their data are a hell of alot harder to change/fix when you screw them up than any domain layer or UI layer.




But in either case....going back to the Attachments example....
I really would like to avoid duplicating the schema of attachment table.
I will be using an ORM and I would end up with a whole bunch of different objects, theres no true inheritance there using option 2.

I think in terms of say a procedural MS-Access Applicatation Option2 would be the way to go, simply to cut down on the amount of joins....but for an ORM mapper Option1. Is that sound logic or am I nutcase?

Would I be mistaken to believe there is no way I can share methods on clientAttachment and salesAttachments unless they were procedural in nature (without comlpex mapping anyway)?

What do you say about this?
--------------
Client
- clientId (pk)
- blobdata
- otherClientAttachmentFields
- etc

Sale
- SaleId (pk)
- clientId(fk)
- blobdata
- otherSaleAttachmentFields

I saw a one to one relationship between Client and its attachements, and a one to one relationship between Sale and it's attachements.

if a client or sale can have more than one attachment, it isn't one-to-one

Correct. Sales and Clients have can have more than one attachment.

If it was 1 to 1 it would work...but I think I would still prefer attachment in its own table in that case as it also raises the problem of duplicating fields. Maybe I'm being pedeantic to avoid that, but I like to write things once and reuse them as much as possible...

It goes back to the OO or procedural front end thing. If programmer people have to write complex code to attach blob data and do various things it'd all have to be procedural to reuse the code.

I thought of a variation of Option3

Client
- ClientID (pk)

Sale
- SaleId (pk)

Attachment
- attachmentId (pk)
- clientId
-SaleId
- AttachmentBlob (The blob Data)

with a constraint on Attachment, to have either clientId, or SaleId present not both in the same time.

But now, I don't think it's that much different from your original Option3, which might be even better than this one.

Well I think that method is better than my version of opton3. (I didnt think of constraints because im from ms-access which doesnt have them, so I'm noob with them)

I don't mind that approach either now that I think about it. It will be the easiest way to maintain I think. I don't think its entirly correct from a purist perspective though.

I'm so confused. I suppose in the end it doesnt really matter which way I go? If in doubt I suppose go the purist route?

Thank you