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
- Attachment (This is a scanned file such as a copy of a drivers liscence)
- 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)
- attachmentId (pk)
- clientId (pk)
ClientAttachment (This is just a link table)
- attachmentId (pk)
- SaleId (pk)
SaleAttachment (This is just a link table)
- 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.
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?
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.
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)?
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.