Hi, I don't agree with this "You should never: * Use primary keys having more than one column. * Actually have any meaningfull data in the key olumn" but I do agree that when possible put use an ID with not semantic meaning. It just simplifies a lot of things
and in reality is sometimes semantically more correct. Performance wise there should be not difference as current databases work very well with composite keys too (Indexation is a turning a set of value into one value, like a map or hash table) so IMHO this
is no motive to have OID in the table as there are several drawbacks too (not the one presented). Fo instance, when doing database merges automatic ID's can turn the process into a nightmare (creating view spaning multiple databases). One can circunavigate
this by using an ID of type GUID, but then there can be a performance hit when doing JOINS. But if you don't have to do nothing like this, then ID such as the one purposed by Thona can be used and should be used IMHO. Nuno
there does come a point when the overhead of creating smallint/int pks in place of a natural primary key (i.e. the space it takes up for having that extra column which is NOT a part of the data) is greater than a char(2) join vs. an int join. i used to subscribe
to the thought that joins should always be done only on int fields. why? what's the real performance gain of that over a char(2)? is it really worth it? and in many cases, having the char(2) meaningful column as the primary key, means you do not have to do
as many joins. in some cases, a meaningful column as a primary key can save you numerous table joins. i think each case should be taken on it's own merits rather than a blanket "always use a single non-meaningful long/int/smallint/tinyint as a pk". just in
the us states example, if you had a user table, each user was in a state, having a StateID, your query (or what your o/r mapper would produce ;) ) to return a list of users with the state they're in might look like this:
select username, statecode from user inner join state on user.stateid=state.stateid
whereas using a natural pk would make the query
select username, statecode from user
makes for a faster more convenient
query and saves on storing an extra field in your database. btw, the us states table was meant to be an example - not as a suggestion to implement :) ditto with the code above.
Nuno, thanks so much for your answer and the lots of material that i really appreciate. I have Asp net Problem, Design, Sollution. What i lost there: Ability to have all layers reflecting a class for each object. They also have too much logic in the SP's. Look,
i'm not talking bad about the book, - contrary- in fact i change to c# at same time the book goes out - because at the time there are few titles with well thinking solutions (are there now?) but follow an design as Modular it's very powerfull for CMS's, but
at some time tie you with many "must do" to work. I'm "trying" follow something like the BO's of Rock Lohkta. Pros: ability of remote themselves with rules, state(if necessary) and work with any layer (UI or DAL) in any machine. Cons: DataAccess members in
the BO. Since i'm envolved in an Intranet solution, i don't think i'm wasting time, at least in the finish my knowledge will be better. By the way, do you know the app to work with legacy by MS? Is an app block named "CrudBlock". I like it. It's from there
that i try thinking about an CRUD interface approach. And i ::Take an Order and its Order Lines (Order [1:N] OrderLine ), are you saying that you will never SELECT All Orders of a given Product?. I'm taking your example as abstract pattern. Sorry, of course
from what i have showed you couldn't see that. But my point is if there are any gain from separate one in two or more to get some specialized behaviour. But you are right, maybe i shouldn't expose any before having all meaningfull. How much for the Borland
tool? Thanks JCasa
"How much for the Borland tool?" It's not cheap. The check the Borland site for current prices. They have an academic licencing agreemen too. "Ability to have all layers reflecting a class for each object." That is true, but as they use a DAO approach. I honestly
think that you will only get to that what you fully want in the following manner: 1) Develop alot of custom code to separate layers. In the end that code will be hardly reusable. 2) Develop your own O/R Mapper but be prepared to spend a lot of time on it.
Other's have been doing that for 2 years and have not yet reached a solution for every scenario (some are 25% there, other 50%, others 80% top IMHO). "They also have too much logic in the SP's." That is true. I don't use the books approach for my own projects.
Neverthless is a good alternative for some - different companies, different cultures. No I have not met the "Crud on the Block" :) But I don't think I want to met with him :) CRUD is a concept that can be mapped in many ways. If that app/framerwork does what
you need you just have to port it. Have fun. Nuno
One more thing, "Sorry, of course from what i have showed you couldn't see that. But my point is if there are any gain from separate one in two or more to get some specialized behaviour." In OO terms yes there is. It all depends on the Problem Domain of course.
Just check the book "Streamlined Object Modeling" for further explanation. It describes 12 Patterns that basically encompasses most business scenarios, ways to aggregate business objects and collaborations between pattern players (really powerfull). Nuno
Nuno, just one thing that could lead you to some mistake because of me. The Crud block isn't an Crud application. It's an complete solution to work with Legacy applications. You acess the Legacy thru Web Services or thru an facade. Happens that to make actions
you use classes Instead others like User Interface Process or Configuration Managment, this one has really utility. Funny, after installed, the name in Start->ProgramFiles->MicrosoftApplicationBlocks for .NET -> is Crudblock. There is an article about it in
the MSDN library. If you like i could see whats the urls. JCasa
"There is an article about it in the MSDN library. If you like i could see whats the urls. " Yes I would like as I cannot find it myself. At least in such terms. Nuno
HEY THONA... ::You should never: ::* Use primary keys having more than one column. but at school they teaching me to use 2 primary-key columns for situations like this: order with an orderID... and the orderlines with an the orderID+orderLineID... is this a
bad design??? if it's Bad, so give me the arguments so i can bug my teacher:D Thanx, Tarek
Bluemagics Weblog: "I'll always make your dark sky blue!"
nbplopes
Participant
1745 Points
349 Posts
Re: Pencil and paper design
Oct 14, 2003 02:58 PM|LINK
m7
Contributor
4225 Points
843 Posts
Re: Pencil and paper design
Oct 14, 2003 03:23 PM|LINK
João Ca...
Member
500 Points
100 Posts
Re: Pencil and paper design
Oct 14, 2003 03:50 PM|LINK
nbplopes
Participant
1745 Points
349 Posts
Re: Pencil and paper design
Oct 14, 2003 05:07 PM|LINK
nbplopes
Participant
1745 Points
349 Posts
Re: Pencil and paper design
Oct 14, 2003 05:20 PM|LINK
João Ca...
Member
500 Points
100 Posts
Re: Pencil and paper design
Oct 14, 2003 07:05 PM|LINK
João Ca...
Member
500 Points
100 Posts
Re: Pencil and paper design
Oct 14, 2003 08:39 PM|LINK
nbplopes
Participant
1745 Points
349 Posts
Re: Pencil and paper design
Oct 14, 2003 08:59 PM|LINK
João Ca...
Member
500 Points
100 Posts
Re: Pencil and paper design
Oct 15, 2003 05:19 AM|LINK
Bluemagics
Participant
1955 Points
391 Posts
Re: Pencil and paper design
Oct 15, 2003 08:35 AM|LINK