Re: Compare with default value?

Ulrich Goebel <ml(at)fam-goebel(dot)de> writes:

> Hi,
>
> Am 13.03.21 um 22:21 schrieb Tim Cross:
>> Ulrich Goebel <ml(at)fam-goebel(dot)de> writes:
>>
>>> Hi,
>>>
>>> o.k. I have to give some more information...
>>>
>>> Am 13.03.21 um 19:39 schrieb David G. Johnston:
>>>> On Saturday, March 13, 2021, Ulrich Goebel <ml(at)fam-goebel(dot)de
>>>> <mailto:ml(at)fam-goebel(dot)de>> wrote:
>>>>
>>>> I would like to get the rows, where a column has the default value,
>>>> similar to:
>>>> select id fromt tbl where col = default
>>>>
>>>> If the default is a simple constant then why go through the trouble instead of
>>>> just writing col = ‘constant’ ?
>>>
>> I think you may need to re-think your design or at least come at it from
>> a different perspective. As shown by another post in the thread, at some
>> level, this is 'sort of' possible, but it will be ugly and fragile.
>
> Yes, I am re-thinking allredy...
>
>> Possibly one of the issues you have is on one hand, you want the
>> solution to be as generic as possible, but on the other, you require it
>> to have specific knowledge about the definition of your table. i.e.
>> which column(s) have a default value. Furthermore, your 'completion'
>> table will need to be defined based on this information i.e. potentially
>> multiple columns with multiple different data types etc.
>> While the additional information you provided does give some increased
>> understanding of what your trying to do, it is still describing your
>> desired solution implementation. It might be better if you provide more
>> high level details of what your attempting to do so that we can better
>> understand how you arrived at the proposed solution and whether there
>> may be better alternatives available.
>
> o.k.: For a conference I have a tbl_person which holds all peoble which are
> involved: participants and people which provide private lodgins for other
> participants. (There are much more roles, but for illustration these two should
> be enough.) Of course each person can have one or more roles at the conference,
> a n-n-relation models that. Now our workflow allows that one person find it way
> in the tbl_person twice (ore even more often): for example the conference office
> generates a row for Tom, because he provides private lodgin. Later on Tom
> decides to participate an fills the online registration formular. These data
> generate the second row for Tom. Both rows hold significant information which
> the other doesn't hold. Let's say the online register gave the birthday, the
> other holds information about the lodgin (bed with or without breakfast). The
> next step then is that the conference office get notice of the doubled person an
> should make one row out of the existing two rows. The office decide which of the
> two rows should be completed with data from the other row. Therefore I would
> like to pick the columns in the first row where we have default values and
> replace it by the value from the second row.
>
> There are more ways to end with two or even more rows per person. May be it
> would have been much better to avoid these possibilities. But for the moment I
> have a given database structure running in productive mode, so it is not easy to
> re-structure the structure or even the workflows...
>

I really hate to say this, but I think you have a major fundamental flaw
in your database design. As soon as I read

> Now our workflow allows that one person find it way
> in the tbl_person twice (ore even more often): for example the conference office
> generates a row for Tom, because he provides private lodgin. Later on Tom
> decides to participate an fills the online registration formular. These data
> generate the second row for Tom. Both rows hold significant
> information....

I thought - "oh dear, this is going to be a problem".

Based on this and your previous post, I suspect your now beginning to
run into maintenance problems with your application database and are
trying to find ways to deal with the issues your encountering. The good
news, it can be fixed. The bad news, it will take a fair amount of work.
The really really bad news is while you may be able to work around some
of the issues, things are just going to snowball and get worse,
requiring increasing amounts of maintenance and increasing amounts of
effort to fix.

Although you have said there is an existing structure in production and
changing it will not be easy, bottom line is that it really is your only
sustainable course of action. Anything else you do will at best delay
the inevitable and at worst could result in far worse consequences (such
as major data loss or curruption resulting from efforts to maintain a
poor data model design). What you need to do is re-examine your data
model e.g. tables, columns, views etc and make sure you are at least
meeting 3rd normal form
(https://www.guru99.com/database-normalization.html)

This is really your only sane solution. There are things you can do with
views and database functions which can isolate/reduce the impact of changing
your underlying database structure on your front end application and
workflows to buy you some time, but these will likely need to be
re-worked eventually as well.

I know this seems like a daunting task which will have significant
impact and is unlikely to be welcomed by senior management, but you
don't have much choice. Having the wrong database design is like trying
to write software with the wrong data abstractions. As things evolve,
adding new functionality and maintaining existing functionality will
become harder and harder. Increasing amounts of time will be spent in
diagnosing and fixing data anomalies and management/clients will become
increasingly frustrated at how long it takes to add/update/extend
functionality. The sooner you bite the bullet and commit to fixing the
underlying database model, the better. The longer you delay, the harder
it will become.

--
Tim Cross