One-to-one relationships

[frinholp]

I have just read a tutorial about defining relationships at this address:

Link

The author states:

The one-to-one relationship is very uncommon in FileMaker and I've been working with FileMaker for a couple of decades and I've seen very, very few instances of a true, really you need this type of relationship. Usually what you do is you just simply take the fields from that other table and place it into your other table so you really have one table at that point. There's really no point in relating one record to one and only one record in another table. Just move the fields over into the other table. It'll make things much easier. By separating them you cause too many troubles with, you know, calculations and scripts and things like that. It's much easier just to work with it in one table.

Is this not bad database design?

I ask this as I am making some changes to tables right now and would like to know if this is sound advise.

In my scenario I have a table of Artists where I hold artist's details. Dependent whether I choose to use these artists or not I hold different details on them e.g if rejected, reason for rejection or if I choose to aprove their work, I hold their commission rate amongst other specific details.

It seems bad design to have fields in a table that are not used.

It makes more sense to hold specific details in different tables such as ApprovedDetails and RejectedDetails with a field in the Artist table specifying their status. These would be joined using a 1-1 relationship.

What are peoples opinions on this? And also how can you get the relationshipship graph to reflect this i.e no crows feet at either end of the join?

Thanks in advance

Lee

[Vaughan]

The only time that separate tables are useful is if you need to update details (in the second table) without editing the data in the main table.

For example, a contact record may have a separate table for address, so the address can be changed without the contact record modification date changing.

It is debatable, of course, that the contact record modification date should change if the address is modified, but this is just an example. Having separate tables adds significant complexity, and it may make fields unstored which can impact performance of finds etc.

Right now I am going through a solution that was developed by somebody else, and they have split tables everywhere. It's a challenge because they have gone all the way to 11 and used one table and related it to multiple others, so there are fields in this table that are used in one part of the solution but not another. I'm going through and aggregating them into one table again. The simplification of the relationship graph is significant and this will reduce development time and maintenance in the future.

[frinholp]

Thanks Vaughn

That makes even more sense now when I look at the amount of information that was actually specific to each table. The only information in the RejectedArtist table was reason for rejection and in the Artist table was biography and status. As status still needs to be held, the extra key fields and mod/gen fields required for each table worked out at 18 extra fields to segregate the three tables and hold two fields elsewhere. Overkill is the word I think.

I have seperated addresses but this is a one to many relationship anyhow and needs to be split.

Thanks again for the advice