This is a serious ERD problem...

[saleemMSMS]

consider the following scenario
there are 3 kinds of departments in a company
the departmnts have seperate IDs and stuff.
the names are,
Financial Department
IT Department
Logistics Department
all these departments are undergoing training
also, he finncial and logistics departments are undergoing monthly assesmenrts
the financial and IT departments are attending the monthly development forums as well
the entities i foud are the

finance department
IT department
Logistics Department
Training
Monthly Assesments
Development Forum

i though of including a superclass to consolidate the 3 deparments but it seems not working since the 3 deopartments has seperate numbering systems
now i'm stucked and am wondering how to draw the ER diagram.

pleas reply..

 

[namliam]

well a department is a department, I dont see "logistics department" or "IT Department" as seperate, but as one... Department.

Same with people/employee's you dont make "managers" "developers" "account managers" etc for each function, you just have "employee"

Same thing with Assessments or fora or what ever, Perhaps some are different in requirements but most can probably be captured using a single "Events" or something or other object.

Atleast that is my take on it

 

[saleemMSMS]

ok lets say that the 3 departments have 3 different numbering systems ? i mean i cannot include them like a superclass sub class thing rite? (i.e an entity called department and the 3 departments are dissociated from it soft of thing ?? )
so how can i show the differences of the 3 departments ?
dude, may be the example is not the most suitable one. but i think u understand the problem well ;-)

 

[namliam]

ok lets say that the 3 departments have 3 different numbering systems ? i mean i cannot include them like a superclass sub class thing rite? (i.e an entity called department and the 3 departments are dissociated from it soft of thing ?? )

And why not? At the root of it all, they are all departments, how do they have different numbering systems? You can easily (should perhaps) make 2 "primary keys" one database (meaningless!!! autonumber) key that is hidden from the normal user, while having a meaningfull (surogate,natural,meaningfull) key for the users to use.... Again this design is quite common even recommended for many a natural, meaningfull keys... No matter how hard people try, meaningfull keys lack one ability a primary key needs... It can NEVER change, and no mater how hard people try... meaningfull data will always at some point in time (may take years) change...
New system, new management, what ever the reason numbering systems for what ever type of system/product/department/employee/etc will always change

so how can i show the differences of the 3 departments ?
dude, may be the example is not the most suitable one. but i think u understand the problem well ;-)

The key of design is to get down to the core, a department is a department
Employee is employee
Product is product
etc...

Common stuff should be stored commonly, while dependent stuff is seperated out... The "basic" 0-1-2-3 normal forms

Appearently your problem is not with departments but with something else, still if the situation is comparable then the solution is comparable.

 

[saleemMSMS]

hm...
ok i think i understand the solution but my problem is hard to explain using this department scenraio. ok hen lets see the real problem.
here we go..
a village has the following.

community based organizations - CBO
they are a small group of villages get to gether and they do some specialized things

community enterprises
are large business organization which spread in many villages

small businesses
small business orgs of a village. localized businesses

producer groups
people who produce crops. farmers etc etc

service providers - SP
people who provide various services such as financial loans etc etc, knowledge etc etc.

all these entities are attending training programs. may be not the same training program, but they attend some kind of training programs. (so all will connect to the training program entity)

CBOs and SPs have meetings

CBOs Community Enterprises and Small Businesses owns assets one can own more than one asset but a given asset is owned by either CBOs, Community Enterprises or Small Businesses

small businesses and community enterprises organize events. one event is organized by a either community enterprise or small business and they can organize more than one event

the numbering is different
i mean the there are only 120 CBOs so 3 digits are enough
but there are 20 SPs so 2 digits are enough to Service providers
as such, most of the IDs are different (mostly from size).
i'm not sure whether we can include them (or consolidate them, bring all the above groups as to be derived from a super class)
please suggest a solution
and thank you very much for your previous informative feedbacks as well

if u need any additional details, please tell..

 

[namliam]

hm...
ok i think i understand the solution but my problem is hard to explain using this department scenraio. ok hen lets see the real problem.
here we go..
a village has the following.

community based organizations - CBO
they are a small group of villages get to gether and they do some specialized things

community enterprises
are large business organization which spread in many villages

small businesses
small business orgs of a village. localized businesses

Only difference between the two is large or small, how will you differentiate between the two?
A business is a business, local, national, international, global, stellar, inter stellar, 1 employee, 10, 100, 1000, 1000000000 I dont care, its a business

producer groups
people who produce crops. farmers etc etc

service providers - SP
people who provide various services such as financial loans etc etc, knowledge etc etc.

People are people... so why shouldnt we, work together oh so naturaly (as the song goes I think)

Again dont particularly see a difference in people appart from perhaps their "function" or "type", people are people.

the numbering is different
i mean the there are only 120 CBOs so 3 digits are enough
but there are 20 SPs so 2 digits are enough to Service providers
as such, most of the IDs are different (mostly from size).

Nonsense... only 120 CBO's? Only 20 SPs?? Nonsense...
No new business is started? None go bankrupt? No such thing as "limited numbering", always assume this to be INfinite not finite.

i'm not sure whether we can include them (or consolidate them, bring all the above groups as to be derived from a super class)
please suggest a solution
and thank you very much for your previous informative feedbacks as well

if u need any additional details, please tell..

As I see it
Business/Organisations
People/Employee's
Eduction/training/events/etc

Then you need some junction tables to "link" business to people to Training.
Perhaps also business to training to indicate that they are required/allowed to give/take/follow that training what ever....

 

[saleemMSMS]

hey then what about the unique IDs of the mentioned Entities.. if i categorized as u said, how am i able to show the IDs and stuff
?

its better if u can explain the ER u're proposing.. also bro this DB should be developed for a humanitarian project.. so thats why there are these targeted number of CBOs and stuff..

 

[namliam]

targetted numbers, TARGET... no such thing as a fixed number in a target... its a guess or guestimate as best...
Also these 'numbers' are unlikely to be numbers IMHO, like 1,2,3,4 etc...
More likely something like AMS001 or something for the first project in Amsterdam or something simular.

I am not saying this is the be all end all of the design there are probably issue's hiding below the surface that I cannot even start to imagine. Core of all design is and has to be the entities, normalize, KISS

For example if you have a small business that spreads to other villages, thus becomming a large business... How is that facilitated?
Does the large business become a new business??
Does it get a new entity??
Does it change ??

If this business was say AMS001, will it then change the NL001 and ultimatly maybe E001??
I think / expect the answers to be no to all questions, once something is labeled its labeled.

However if still some of this is Yes, that dont break 'my' idea's rather makes them stronger as for example AMS001 does change, then that would be your PK changing??

 

[stopher]

I’ll chip in so that you get more confidence with what namliam is saying (I fully agree with him by the way).
Things like “small”, “large”, “village” , “CBO” are just facts about a business. So as namlaim says, we are just talking about a “business” entity. Maybe an example table will help:

BusinessID__BusinessReference______BusinessName____________________BusinessSize______BusinessType
1_____________HM103434_________Niceville Community Centre___________Small__________Community
2_____________AAA1______________Anytown workshop__________________Small__________Work Experience
3_____________00012345___________Johns Farm_______________________Medium_________Work Experience
4_____________AID1_______________Jolly Village_______________________Small___________Community

It doesn’t matter that you may want to have training records for “work experience” businesses but not for “communities”. This entity doesn’t deal with training records. It just deals with all the information you would need to record about a business e.g. contacts, addresses, telephone numbers etc. You will then have other entities that will have training records or production records or whatever is appropriate to the difference business types.
This is very much the essence of normalisation and don’t worry, it does take a while for the concept to sink in.
Hth
Chris

 

[dportas]

meaningfull keys lack one ability a primary key needs... It can NEVER change

Does a primary key truly need to be unchangeable? Answer: no it doesn't. It's true that changing key values may need special consideration in database design and may present some practical problems but in principle and in many cases in practice there is nothing fundamentally wrong with having any candidate key (and therefore a primary key) based on an attribute that users can change. Stability of keys is often desirable but it is definitely not essential.

A good example is a user login name. It's quite reasonable to change a user's login name as long as the name remains unique at all times. You may or may not want to use a surrogate key as well but that's a different matter.

I mention this only because I've seen similar comments twice recently (that a primary key "must never, ever" be updated) and it bothers me that some people may have the impression there is some kind of practical or theoretical limitation that prevents key attribute values from changing. That's not the case.

In the relational model the idea of updating any attribute is a pretty loose concept anyway. More precisely an "update" is an asssigment operation where one set of tuples in a relation variable gets replaced by a different set of tuples. Since a tuple is identifiable only by its attribute values it is not very accurate to talk of any attribute value being "updated". But as I said, this applies equally to every attribute and there's no reason to single out key attributes as a special case.

For the avoidance of doubt, none of the above should be interpreted as an argument for or against the use of surrogate keys. I'm simply talking about the properties of candidate keys (including primary keys) generally.

Sorry for this over-long diversion from the main topic of the thread but I just wanted to set the record straight here.

 

[namliam]

While in theory you are right, in practice changing a primary key is WAY more hassle than it is worth...
I have done it a few times due to working as a contractor and let me tell you it is a nightmare with all kinds of cascade updates that need to happen. Particular if you start a discussion about a varchar/text going to number (or to date or reverse) IT IS a frigging nightmare.

I think it is solid advice for the "average" user to NOT use anything that may ever change as a primary key.
Same thing goes for composit keys, both these things come with their own sets of problems that will cause major headaches

 

[The_Doc_Man]

dportas, I believe that a primary key needs to be unchangeable because when you make it a PK, that field takes on a "metadata" attribute that other candidates do not have. Due to parent/child relationships and the need to maintain relational integrity, you want to isolate your record-identifying metadata from all natural/normal data.

I understand that when you use natural keys you will have the temptation or even outright requirement to sometimes change the key value. If you built your DB to have this facility because you knew it was a requirement, then what the heck, go ahead and change the keys, rippling everything down. However, I think you would find it EASIER in Access - and in many other modern relational systems - if you had some sort of metadata to identify the rows in which you wanted to make changes to child data elements.

Saleem, I believe your problem is that you have not fully normalized or analyzed your data. A crucial and totally unavoidable part of using a database to model something is that you must isolate all entities in a way that they are fully abstractable using a consistent entity property set. If you cannot do that, either you are retaining something that shouldn't be part of the entity or the entities really are not compatible and therefore do NOT belong together. Your comments about "different numbering systems" is to me evidence that the numbering systems don't belong with the entities that have such systems. In such cases, the numbering system differences cry out for a synthetic key and the actual numbers are just data elements at a lower level of the entity diagram. The business about strong/weak (which appears in a related question that you posted) is - trust me on this - smoke screen for database design. It exists only because some government entity is using some modeling standard that you have to learn how to avoid rather than use. Because the distinction is (I'll be blunt here...) dreck.

Good luck.

 

[dportas]

I believe that a primary key needs to be unchangeable because when you make it a PK, that field takes on a "metadata" attribute that other candidates do not have. Due to parent/child relationships and the need to maintain relational integrity, you want to isolate your record-identifying metadata from all natural/normal data.

I read this a few times and I don't understand it. Whatever you "believe" there is nothing in principle in the relational model that stops you changing a primary key or changing values in primary key attributes. All DBMSs I know of permit such changes. If you know of some system that doesn't allow you to change your mind then please tell me so that I can make a mental note to avoid that system!

Of course there are practical consequences to any change made to the data model or the data - that after all is why we would want to make a change in the first place. So the decision on whether or not to make changes needs to be decided on the merits of each case. That's really what I wanted to say.

 

[The_Doc_Man]

Of course there are practical consequences to any change made to the data model or the data

Hence my comment about - if you knew this would be a requirement ahead of time, go ahead and make the change. I also agree that it is perfectly allowed in a database to change the values of your PK. But I worry about whether your description is accurate if the PK is mutable over time. Because it at least implies that the values in the table don't completely depend on the PK, they also depend on a date. And that "extra, external" dependency somehow bothers me from a theorist's viewpoint.

On the other hand, I'm pragmatic enough to reiterate - do it if you feel it is necessary and proper. Requirements ALWAYS drive design, not the other way around.

My comments about the PK and metadata are simply that before you choose the PK and after you choose the PK, your table is structurally different. That difference is the choice of a PK. You change structures by changing the metadata that describes the structures. So the PK must have taken on a metadata attribute in some odd way. If you don't like that train of thought, don't follow it. I've been known to wander down strange garden paths before.

 

[dportas]

I also agree that it is perfectly allowed in a database to change the values of your PK. But I worry about whether your description is accurate if the PK is mutable over time. Because it at least implies that the values in the table don't completely depend on the PK, they also depend on a date. And that "extra, external" dependency somehow bothers me from a theorist's viewpoint.

It shouldn't do because they are two different tuples. Assuming A is key and B is not then A -> B certainly does not imply that B -> A.

My comments about the PK and metadata are simply that before you choose the PK and after you choose the PK, your table is structurally different. That difference is the choice of a PK. You change structures by changing the metadata that describes the structures. So the PK must have taken on a metadata attribute in some odd way. If you don't like that train of thought, don't follow it. I've been known to wander down strange garden paths before.

That is really an implementation detail. In principle a primary key need not be distinguishable from other candidate keys in the database at all. "Primary" keys are of no special significance in the relational model. In SQL there is an entirely optional constraint called a "PRIMARY KEY" but you aren't compelled to use it because a NOT NULL UNIQUE constraint achieves just the same thing. So there may be metadata identifying a primary key but there doesn't have to be.

 

[The_Doc_Man]

Just some theoretical musing anyway. As long as you are careful about changing your PK, have fun. I just dislike it because it has some interesting side effects if you are keeping strict audit trails. Perhaps that is why I take the view that I do.

If you are in an environment where you have strict auditing requirements (such as the USA Dept. of Defense, where I work), you CANNOT change a PK if doing so would invalidate historical auditing records. (Not allowed.) That might help you to understand my concern a bit more.