Hello,

How categories are mapped to physical design, an example is that, the car owner can be a bank or person i.e. How owner table should look like in the physical design below. This example I took from fundamentals of database management system, I have one design in my mind using generalization concept i.e the person and the bank are a sub type of owner and I can map these relations using 1-1 keys. But I need another design because I have tables with data and I need a lot of effort to do the first design.



band (bank_id, banke_name ...)
person(ssn, first_name,....)
owner (id integer, )
car (owener_id, car_name, ....)

Regards

sounds to me like the generalization concept (supertype/subtype) is exactly what you need

can you explain in some more detail why you can't use it?

Simply put, In the generalization concept you need to make sure that the keys are disjoint in the sub tables and I have two tables already populated and each table has many relationships with other tables in the schema. if I want to change the keys I need to write script to test the relation because in the existing design the cascade options are set to no action.

i.e
in sub table 1 i have ids 1 .....n
in sub table 2 I have ids 1.....n

if I want to do generalization I need to change the domain of the keys for one table such that
subtable 2 ids in n+1 .....m and then propegate the changes manually becasue I have 'on update no action' in tables which reference these tables

okay, i understand

so, what are you trying to add to this existing database? and why?

I have two entities have the same relationship to another entity. the proper solution would be generalization and map the relation on the parent level. However, I need to see if there another solution.

The easiest way without going back and making an entity id for all entities, so that there are no duplicates would be to add the type to the owner table.

owner (id integer, idtype char(1))

You would not be able to define a foreign key to both of your parent tables, it would have to be application controlled RI or trigger controlled(a check to ensure the ID exists in one of the two parents).

Dave Nance

Would something like this help?-PatP

quite likely

aside: what is this mysterious "GO" of which you speak?

"GO" is a convenient way to limit the chaos within the tool at hand... It separates SQL statements into practical groups so that the interpreter knows where the coder thinks a logical division is, so that if/when things run amok it is easier to "put things back on the rails" because then the interpreter knows where a logical "chunk" starts.

-PatP

you must've either overlooked or ignored the smiley in my post

this thread is in the Database Concepts & Design forum, so proprietary, non-standard SQL should be avoided

you sycophantic microsoft fanboy, you