"Fixed "Relational Design vs Business evolution

[Ugo DI LUCA]

Hi,

I've got bad feelings about how to standardize a Relational database in a business environment, where many "parameters" can evolve along with the business itself.

Many classics One To One relationships are driving me mad :

- an agent may be "employed" by 2 different companies, but the standards are that one contact belong to one Company,

- a Product may be unique or part of a set or service

..... and many other "odd" situations

The more I go on the developpement of the db I'm currently on, the more I feel like there is quite no reason to handle a One To One relationship. I realize even a good Database Map does'nt solve all problems, but....

Why not immediately create One To Many relationships as it is easier to go back to a One To One (having only one related record) that moving from a One To One to a One To Many.

Any ideas here or does it makes no sense for any of you ?

[danjacoby]

Never understood the need for "one-to-one", since all that data can be in the same single record. The advantage to relationships lies in their "many" possibilities -- especially "many-to-many".

As far as DB design/development goes, one thing that drives all of us crazy is when we're halfway through creating the whole thing, when the client suddenly mentions "a few minor things" he/she "overlooked" at our preproduction meetings. They never seem to understand why I have to go back and undo half of what I created just to put these "few minor things" in.

C'est la vie.

[Ugo DI LUCA]

Yeah !

Depending on what this minor thing is, the whole setup could even be wrong.

I'll make them have a jump into The Seine in a cold winter day...

Did you try the Hudson River ? Keeps you awake for a long long time, even in the Summer

[djgogi]

The advantage to relationships lies in their "many" possibilities -- especially "many-to-many".

I'm not aware of any DBMS with embeded "many-to-many" relationship management.

(I don't think there is such system).

I feel like there is quite no reason to handle a One To One relationship

Sometimes you just have to use it:

a)Let's say you need to create an DB where some of records need to have some extra informations. The most flexible solution is to create an other table containing extra coulumns (fields) with 1-1 rel based on recID.

Of course you could add those extra fields to your original table and use flags to handle navigation, data insertion ect.( and you'll need to do it each time you'll need to make some "minor" changes to record trace)

or

b)Your table consists of very large number of columns and the file size is growing dangerousely.

Solution :split the records in more tables with 1-1 rel based on recID, each having only subset of columns (fields) from original table, or as Ray would say

do "vertical separation (Vsep)"

Dj

[Ugo DI LUCA]

Hi DJ,

For sure I wasn't talking about the "constant relationships" used to pass some datas from one db to another.....

Vertical Separation seems to one of these "many" clever idea from Ray, but I can't get any posts about vertical separation or vsep.

Could you drive me on any article oon this subject ?

Thanks for the feedback

[djgogi]

Hi Ugo,

well, neither do I was talking about constant rels in a).

As for what stated in : it's the same principle under different circumstances.

I've mentioned Ray (cobaltsky), because of his excelent explanation of this principle (and other extentions of it) in

this article

Dj

[Ugo DI LUCA]

OK, thanks for the link Dj,

Well hopefully I don't feel like my solution may need a table separation architecture, but it's good to know this can be done.

About your a)

>Let's say you need to create an DB where some of records need to have some extra informations. The most flexible solution is to create an other table containing extra coulumns (fields) with 1-1 rel based on recID.

Of course you could add those extra fields to your original table and use flags to handle navigation, data insertion ect.( and you'll need to do it each time you'll need to make some "minor" changes to record trace).<

Sorry I don't get it. That's why and when theory hurts my brain

Let me add another example for the problem I'm describing :

I handled a file re-struture regarding my Company File where each Operator was stored (Customer, Supplier, Other, ...).

At a start, each operator had its own account using the Unique_Id system, but recently several of "my companies" decided to "merge" into ones.

For major complication :

- one of the new "merge" wants to keep separate accounting systems for any of its subsidiaries, but each of these entities had its own orders. It 'll come to a mess when I'll need to invoice a group while the orders where placed by its branches.

- another decided to really "merge", so I have to transfer all previous contacts from the "absorbed" company to the Mother.

So finally I thought "Why not create all new Operator through a Line Item ?"...using a one to many relationship. Not sure this can be called Recursion System.

and as I came up with many of these "odds", I wondered what should be done to anticipate these situations.

[djgogi]

Hi again Ugo,

let's take example of an Personnel table which list all employees of some imaginary company.

Among them you

[Ugo DI LUCA]

Hi,

Got it.

Just to confirm I'm a complcated guy, would you use repeating fields in that PersonnelExtra for adresses, phones or would you again create a joinflle for a One to Many realationship from PersonalExtraToMoreExtra ?

And the use your renumberal portal technique to lookup the Default adress, phone,...

[djgogi]

For simple multi attributes I would (I'm using) use repeated fields.

The method is intended for real attributes that are proper only for certain records

Dj

[djgogi]

Now looking at example you have provided I think that structure

Company----<Subsidiaries (1-many rel based on companyID)

is the starting point.

Dj

[Ugo DI LUCA]

Hi,

Company----<Subsidiaries (1-many rel based on companyID)

With a Subsidiaries file ?

I first looked at that solution with a SubsidiaryToGroup File, with checkboxes "part of a group" or "Main Group" and a portal showing eventual related records, where both Group and Subsidiary also have their own unique_Ids.

Then using another checkboxes set to identify :

- those to be invoiced in group, thus returning the Group Id in a calc

- those to invoice separately.

Is it that ?