Advice on Foreign Keys

Posts   
 
    
smcculloch avatar
smcculloch
User
Posts: 7
Joined: 01-Oct-2003
# Posted on: 12-Dec-2003 07:28:52   

Hi Guys,

I have a very simple database that I am using to test out the functionality of llblgen to present to other people in my organisation.

Here is the DB design,

I have two problems:-

Firstly, can you control how deep some of the relationship go? And remove some of the ones that are not appropriate.

For example, from the User Entity, I don't really need to get the complete collection of IssueStatus Entities assigned to me, I am more likely to get just all the Issues Assigned to me.

Secondly, I thought I had named my relationships properly, but when I look at them in the designer, I get the following:-

For the User Object, I get Issue, Issue, Issue, Issue__

These are for the relationships back to Issue, these being OpenedBy, UpdatedBy, AssignedTo, ResolvedBy

I would really like these to be the same as the relationship name.

Perhaps I am doing something really stupid, if anyone can point me in the right direction it would be most appreciated, frowning

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 12-Dec-2003 10:01:22   

It will be possible exclude relations in the designer, with the small gui updates that are planned in January. I don't think it's wise to have 'UpdatedBy' in the Issue record, because then you can have just 1 update. Better create a separate log table where you can store per issue-update a row.

The naming routine of the fields mapped on relations is trying to create a new field, but these are existing, so what it does is then adds a "_". Looking at your model a good naming algorithm would be: FK fieldname + related entity name. However what to do with m:n relations? simple_smile

You have to rename these fields into what is ok for you to represent the relation, like AddedByUser or ResolvedByUser

Btw, if you want I can mail you the visio vsd with the E/R model and ORM model of a bug/issue tracker database I designed some time ago, I never wrote the software on top of it though, the database is pretty ok.

Frans Bouma | Lead developer LLBLGen Pro
Posts: 15
Joined: 01-Oct-2003
# Posted on: 12-Dec-2003 10:49:21   

Otis wrote:

It will be possible exclude relations in the designer, with the small gui updates that are planned in January. I don't think it's wise to have 'UpdatedBy' in the Issue record, because then you can have just 1 update. Better create a separate log table where you can store per issue-update a row.

The naming routine of the fields mapped on relations is trying to create a new field, but these are existing, so what it does is then adds a "_". Looking at your model a good naming algorithm would be: FK fieldname + related entity name. However what to do with m:n relations? simple_smile

You have to rename these fields into what is ok for you to represent the relation, like AddedByUser or ResolvedByUser

Btw, if you want I can mail you the visio vsd with the E/R model and ORM model of a bug/issue tracker database I designed some time ago, I never wrote the software on top of it though, the database is pretty ok.

Ahh right it makes sense now, I had called the releations exactly the same as the field names. flushed

Look forward to seeing the GUI updates, it would be very useful to exclude some of the relations not really used.

If you could send me a copy of the bug/issue tracker then that would be great, we are just using this as a test app to test new concepts, and to allow newer developers to work on something in there free time. Thanks for picking up the UpdatedBy field. Is this kind of the naming standard you use in your databases?

Could you send it to smcculloch@iinet.net.au

Thanks again for all your help smile

dava avatar
dava
User
Posts: 9
Joined: 17-Sep-2003
# Posted on: 12-Dec-2003 10:51:00   

Otis wrote:

Btw, if you want I can mail you the visio vsd with the E/R model and ORM model of a bug/issue tracker database I designed some time ago, I never wrote the software on top of it though, the database is pretty ok.

I'd be very grateful to receive this, I have a project coming up that requires an element of issue/bug tracking and your design would certainly save me work stuck_out_tongue_winking_eye

Many thanks!

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 12-Dec-2003 12:02:51   

I uploaded a zip: http://www.llblgen.com/pages/files/Bugtracker_ORMandER.zip

(1MB)

It contains 2 vsd files, one with all the ORM models and one with the ER model. It requires Visio for enterprise architect. It also contains an .SQL file which creates the Bugtracker database in sqlserver and adds all the tables and fk's.

If you create an LLBLGen Pro project of this, be sure to set in the project properties the strip pattern for tables to 'BT', as all tables are prefixed with 'BT' (I designed this a while back wink )

Note: it might be I forgot to add some ORM constraint objects to the model. Note2: the 'access right' structure is hard-modelled into the model. This is one way to do it, you can also use a more flexible model of course by adding a role-based security model.

Frans Bouma | Lead developer LLBLGen Pro
wdevine
User
Posts: 6
Joined: 08-Dec-2003
# Posted on: 12-Dec-2003 23:22:36   

I'm not sure whether I should start a new thread for this, but it's kind of related to this discussion since we were talking about the Visio diagrams.

I just picked up the book "Database Modeling with Microsoft Visio For Enterprise Architects" by Terry Halpin et al. Just skimming over it, it looks pretty good. I was wondering if anyone else has read it and what their thoughts were on it.

Posts: 15
Joined: 01-Oct-2003
# Posted on: 14-Dec-2003 13:28:26   

Otis wrote:

It will be possible exclude relations in the designer, with the small gui updates that are planned in January. I don't think it's wise to have 'UpdatedBy' in the Issue record, because then you can have just 1 update. Better create a separate log table where you can store per issue-update a row.

The naming routine of the fields mapped on relations is trying to create a new field, but these are existing, so what it does is then adds a "_". Looking at your model a good naming algorithm would be: FK fieldname + related entity name. However what to do with m:n relations? simple_smile

You have to rename these fields into what is ok for you to represent the relation, like AddedByUser or ResolvedByUser

Btw, if you want I can mail you the visio vsd with the E/R model and ORM model of a bug/issue tracker database I designed some time ago, I never wrote the software on top of it though, the database is pretty ok.

Hi Frans,

I have renamed the relationships, but am not having any luck.

For example, I renamed the relationship :-

FK_Issue_OpenedBy

becomes

FK_Issue_OpenedByUser

Is there something I am missing or doing wrong?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 14-Dec-2003 13:54:18   

Are you renaming the physical FK constraint or the field mapped on teh relation in the entity properties editor? because you should do the latter simple_smile

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 15-Dec-2003 10:12:58   

wdevine wrote:

I'm not sure whether I should start a new thread for this, but it's kind of related to this discussion since we were talking about the Visio diagrams.

I just picked up the book "Database Modeling with Microsoft Visio For Enterprise Architects" by Terry Halpin et al. Just skimming over it, it looks pretty good. I was wondering if anyone else has read it and what their thoughts were on it.

I've an older book of Halpin (with Nijssen) about NIAM, which is the predecessor of ORM (some say ORM is not the successor but another method, Nijssen still developes NIAM afaik). Halpin is a good teacher and designed ORM (with others), and is the driving power behind the Visio designer for ORM. So if anyone could tell all the ins and outs about ORM and visio it's Halpin simple_smile Check out his site also: www.orm.net

Frans Bouma | Lead developer LLBLGen Pro