SQL help

Fri Jan 3 17:14:49 CST 2014

The problem is that the voyage ID I am using is the one for U.S.
customs which is a one letter code for vessel (even though in the
database each vessel has a number ID) and then two digits for year and
then two digits for the sequence of the vessel arrival for that year.

For example H1401 is the first arrival for vessel "H" actually that
ship the Midnight Coast is number 138 in the vessel table. It turns
out very sub-optimal. I should have had it be vessel number 138-033
and so on for ever single voyage of that vessel for my ID field in the
voyages and not use the voyage ID required by Customs (even though it
is unique) as it's difficult to extract from H1401 that it is the 33nd
voyage of vessel number 138. So now the best thing to do would be make
an auto-increment field that is a combination of vessel ID and
sequence of that vessel.

This kind of sub-optimal database design happens sometimes.  So the
problem I'm having is easily asking for the voyage immediately
preceding a particular voyage of a particular vessel. It isn't easy to
extract that using the voyage ID as that changes increment each year.
It also is a different format depending on whether it's a passenger
vessel or a cargo vessel (passenger vessel has a two-letter code).

Bill

On Fri, Jan 3, 2014 at 7:07 PM, Bart Pietercil
<bart.pietercil at cognosis.be> wrote:
> Hmm, it seems to me that your db schema is suboptimal here but without
> knowing the schema this remains of course guessing.
> I would  expect your schema to be something like this
>
> tbl_voyages
>
> voyage_id,
> fk_vessel_id,
> from,
> to,
> departure,
> arrival,
> ….
>
>
> tbl_vessels
>
> vessel_id,
> vessel_name,
> vessel_capacity,
> …
>
> both voyage_id and vessel_id are sequences.
> fk_vessel_id is the foreign key holding the vessel_id for the vessel that is
> doing the voyage (voyage_id).
>
> If this is your schema then please elaborate on the problem your having.
>
> regards
>
> bart
>
>
> —
> Our office will be closed on:
> 24 (afternoon) - 25 - 31/12/2013 & 01/01/2014
>
> Bart Pietercil
> Sent with aMac
>
> On 3 Jan 2014 at 23:55:08, william humphrey (bill at bluewatermaritime.com)
> wrote:
>
> Bart
>
> Thank you for the clear description. I'm still trying to decide which
> would be more useful for me. There are like thirty vessels and each
> new voyage will be for one of the vessels. Only one vessel in each new
> voyage. But if I just auto increment for voyages it is not as useful
> as auto increment that is unique only to each vessel for that voyage.
> That would mean the field would not be a unique value for the voyages
> as it would only be the combination of vessel number and voyage number
> only that is unique.
>
> Bill
>
> On Fri, Jan 3, 2014 at 5:56 PM, Bart Pietercil
> <bart.pietercil at cognosis.be> wrote:
>> Theoretically you can. What I don’t know is whether the Valentina Engine
>> already supports it.
>> The sequence generator has been added to vStudio in order to support the
>> Postgresql engine.
>> In Postgresql you would do
>>
>> add field vessel_nr serial (or big serial).
>> This is shorthand for
>>
>> add field vessel_nr integer (or bigint) unique
>> create sequence seq_vessel_nr
>> set default value of field vessel_nr to nextval(seq_vessel_nr)
>>
>> so now you have a field vessel_nr with an autoincrementing value on every
>> insert (creation of vessel trip?)
>> the only thing left to do is , if you already have data in your vessel
>> table, generate numbers for the existing values.
>> And the last thing to do is set the startvalue of seq_vessel_nr to
>> max(vessel_nr)+1.
>>
>> From hereon your vessel_nr will autoincrement with the correct number
>>
>> hth
>>
>> bart
>> —
>> Our office will be closed on:
>> 24 (afternoon) - 25 - 31/12/2013 & 01/01/2014
>>
>> Bart Pietercil
>> Sent with aMac
>>
>> On 3 Jan 2014 at 16:47:59, william humphrey (bill at bluewatermaritime.com)
>> wrote:
>>
>> Can that kind of create sequence be done after the fact of table
>> finished and data already in?
>>
>> On Fri, Jan 3, 2014 at 11:23 AM, Ruslan Zasukhin
>> <ruslan_zasukhin at valentina-db.com> wrote:
>>> On 1/3/14, 4:46 PM, "william humphrey" <bill at bluewatermaritime.com>
>>> wrote:
>>>
>>>> I've been thinking that a sequence maintained with a trigger might be
>>>> very useful for other things too. I never thought of numbering
>>>> sequentially the voyages of each vessel. It solves some other
>>>> problems. Right now voyage numbers increment from zero at the start of
>>>> each new year (which is what caused the problem) but there is no
>>>> reason why I can't have an extra field which has every voyage for that
>>>> vessel down through the years.
>>>
>>> Do you talk about CERATE SEQUENCE?
>>>
>>>
>>> For simple case, consider SERIAL32 or SERIAL64 pseudo-types,
>>> As even more simple things.
>>>
>>>
>>> --
>>> Best regards,
>>>
>>> Ruslan Zasukhin
>>> VP Engineering and New Technology
>>> Paradigma Software, Inc
>>>
>>> Valentina - Joining Worlds of Information
>>> http://www.paradigmasoft.com
>>>
>>> [I feel the need: the need for speed]
>>>
>>>
>>> _______________________________________________
>>> Valentina mailing list
>>> Valentina at lists.macserve.net
>>> http://lists.macserve.net/mailman/listinfo/valentina
>>
>>
>>
>> --
>> http://www.bluewatermaritime.com
>> _______________________________________________
>> Valentina mailing list
>> Valentina at lists.macserve.net
>> http://lists.macserve.net/mailman/listinfo/valentina
>>
>>
>> _______________________________________________
>> Valentina mailing list
>> Valentina at lists.macserve.net
>> http://lists.macserve.net/mailman/listinfo/valentina
>>
>
>
>
> --
> http://www.bluewatermaritime.com
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina
>
>
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina
>

-- 
http://www.bluewatermaritime.com