Question on spot-class-map entities(copied from jira-1590)

[Valentina Sirkova]

comment from karnchana pangwan:

Now I created three entities and added new example data and extended workeffort, but I not sure about my data model. In "nrOfSpaces" of AccommodationSpot I added space for person. With my demo provide customer select fixedasset,class and tables number (get accommSpotId into workeffort) and input time for make reservation (restaurant).

Can you check it again about my data?


My data
--------------------------

<?xml version="1.0" encoding="UTF-8"?>
<entity-engine-xml>

    <FixedAsset fixedAssetId="Restaurant" parentFixedAssetId=""
        fixedAssetName="Restaurant" locatedAtFacilityId="Tableservice"/>
    <FixedAsset fixedAssetId="Private Rooms" parentFixedAssetId="Restaurant"
        fixedAssetName="Private Rooms" locatedAtFacilityId="Tableservice"/>
    <FixedAsset fixedAssetId="Communal tables" parentFixedAssetId="Restaurant"
        fixedAssetName="Communal tables" locatedAtFacilityId="Tableservice"/>
    <FixedAsset fixedAssetId="Bar/Lounge" parentFixedAssetId="Restaurant"
        fixedAssetName="Bar/Lounge" locatedAtFacilityId="Tableservice"/>
   

    <AccommodationClass accommClassId="Tables" parAccommClassId="" description="Tables"/>
    <AccommodationClass accommClassId="VIPTables" parAccommClassId="Tables" description="VIP table"/>
    <AccommodationClass accommClassId="PartyTables" parAccommClassId="Tables" description="Party Table"/>
    <AccommodationClass accommClassId="Chairs" parAccommClassId="" description="Chairs"/>
   
   
    <AccommodationMap accommClassId="Tables" fixedAssetId="Restaurant" nrOfSpaces="20" overbooked="0"/>
    <AccommodationMap accommClassId="PartyTables" fixedAssetId="Private Rooms" nrOfSpaces="3" overbooked="0"/>
    <AccommodationMap accommClassId="VIPTables" fixedAssetId="Private Rooms" nrOfSpaces="5" overbooked="0"/>
    <AccommodationMap accommClassId="VIPTables" fixedAssetId="Communal tables" nrOfSpaces="3" overbooked="0"/>
    <AccommodationMap accommClassId="Tables" fixedAssetId="Communal tables" nrOfSpaces="5" overbooked="0"/>
    <AccommodationMap accommClassId="Chairs" fixedAssetId="Bar/Lounge" nrOfSpaces="10" overbooked="0"/>
   
   
    <AccommodationSpot accommSpotId="1" accommClassId="PartyTables" fixedAssetId="Private Rooms" nrOfSpaces="30" accomNumber="PrPy-1"/>
    <AccommodationSpot accommSpotId="2" accommClassId="PartyTables" fixedAssetId="Private Rooms" nrOfSpaces="30" accomNumber="PrPy-2"/>
    <AccommodationSpot accommSpotId="3" accommClassId="PartyTables" fixedAssetId="Private Rooms" nrOfSpaces="30" accomNumber="PrPy-3"/>
    <AccommodationSpot accommSpotId="4" accommClassId="VIPTables" fixedAssetId="Private Rooms" nrOfSpaces="2" accomNumber="PrVIP-1"/>
    <AccommodationSpot accommSpotId="5" accommClassId="VIPTables" fixedAssetId="Private Rooms" nrOfSpaces="2" accomNumber="PrVIP-2"/>
    <AccommodationSpot accommSpotId="6" accommClassId="VIPTables" fixedAssetId="Private Rooms" nrOfSpaces="2" accomNumber="PrVIP-3"/>
    <AccommodationSpot accommSpotId="7" accommClassId="VIPTables" fixedAssetId="Private Rooms" nrOfSpaces="4" accomNumber="PrVIP-4"/>
    <AccommodationSpot accommSpotId="8" accommClassId="VIPTables" fixedAssetId="Private Rooms" nrOfSpaces="4" accomNumber="PrVIP-5"/>
    <AccommodationSpot accommSpotId="9" accommClassId="Tables" fixedAssetId="Communal tables" nrOfSpaces="4" accomNumber="CmT-1"/>
    <AccommodationSpot accommSpotId="10" accommClassId="Tables" fixedAssetId="Communal tables" nrOfSpaces="4" accomNumber="CmT-2"/>
    <AccommodationSpot accommSpotId="11" accommClassId="Tables" fixedAssetId="Communal tables" nrOfSpaces="6" accomNumber="CmT-3"/>
    <AccommodationSpot accommSpotId="12" accommClassId="Tables" fixedAssetId="Communal tables" nrOfSpaces="6" accomNumber="CmT-4"/>
    <AccommodationSpot accommSpotId="13" accommClassId="Tables" fixedAssetId="Communal tables" nrOfSpaces="6" accomNumber="CmT-5"/>
    <AccommodationSpot accommSpotId="14" accommClassId="VIPTables" fixedAssetId="Communal tables" nrOfSpaces="10" accomNumber="CmVIP-1"/>
    <AccommodationSpot accommSpotId="15" accommClassId="VIPTables" fixedAssetId="Communal tables" nrOfSpaces="10" accomNumber="CmVIP-2"/>
    <AccommodationSpot accommSpotId="16" accommClassId="VIPTables" fixedAssetId="Communal tables" nrOfSpaces="10" accomNumber="CmVIP-3"/>
    <AccommodationSpot accommSpotId="17" accommClassId="Chairs" fixedAssetId="Bar/Lounge" nrOfSpaces="1" accomNumber="brc-1"/>
    <AccommodationSpot accommSpotId="18" accommClassId="Chairs" fixedAssetId="Bar/Lounge" nrOfSpaces="1" accomNumber="brc-2"/>
    <AccommodationSpot accommSpotId="19" accommClassId="Chairs" fixedAssetId="Bar/Lounge" nrOfSpaces="1" accomNumber="brc-3"/>
    <AccommodationSpot accommSpotId="20" accommClassId="Chairs" fixedAssetId="Bar/Lounge" nrOfSpaces="1" accomNumber="brc-4"/>
    <AccommodationSpot accommSpotId="21" accommClassId="Chairs" fixedAssetId="Bar/Lounge" nrOfSpaces="1" accomNumber="brc-5"/>
    <AccommodationSpot accommSpotId="22" accommClassId="Chairs" fixedAssetId="Bar/Lounge" nrOfSpaces="1" accomNumber="brc-6"/>
    <AccommodationSpot accommSpotId="23" accommClassId="Chairs" fixedAssetId="Bar/Lounge" nrOfSpaces="1" accomNumber="brc-7"/>
    <AccommodationSpot accommSpotId="24" accommClassId="Chairs" fixedAssetId="Bar/Lounge" nrOfSpaces="1" accomNumber="brc-8"/>
    <AccommodationSpot accommSpotId="25" accommClassId="Chairs" fixedAssetId="Bar/Lounge" nrOfSpaces="1" accomNumber="brc-9"/>
    <AccommodationSpot accommSpotId="26" accommClassId="Chairs" fixedAssetId="Bar/Lounge" nrOfSpaces="1" accomNumber="brc-10"/>

</entity-engine-xml>

[Valentina Sirkova]

Karnchana,

As far as I see from your data, you model the following(please correct me if I am wrong):
You model restaurant which has 2 rooms and they all are fas. The rooms are related to the restaurant via parent-child relationship. They all are located at the "TableServices" facility.
 - In the Map entity you define the total nr of tables and chairs in the restaurant and the 2 rooms(the "Private and Communal")
 - In the spot entity you model the capacity of each table/chair. And the tables/chairs are spots.

If I got it right then I would say this data structure is fine. However I would like to make the following remarks:
Keep in mind that whatever you put in the spot entity is what can be booked. This means if for example you have the following record from your data:
  <AccommodationSpot accommSpotId="8" accommClassId="VIPTables" fixedAssetId="Private Rooms" nrOfSpaces="4" accomNumber="PrVIP-5"/>
when you make reservation the WHOLE table will be BOOKED!All the  4 spaces as the spotId is unique!

If you want to avoid this and make PARTIAL bookings I would suggest one of the following:

1) Generate as much SPOT records as needed to reach the total capacity of the table. In this case it will be:
 <AccommodationSpot accommSpotId="9" accommClassId="VIPTables" fixedAssetId="Private Rooms" nrOfSpaces="1" accomNumber="PrVIP-5"/>
 <AccommodationSpot accommSpotId="10" accommClassId="VIPTables" fixedAssetId="Private Rooms" nrOfSpaces="1" accomNumber="PrVIP-5"/>
 <AccommodationSpot accommSpotId="11" accommClassId="VIPTables" fixedAssetId="Private Rooms" nrOfSpaces="1" accomNumber="PrVIP-5"/>
 <AccommodationSpot accommSpotId="12" accommClassId="VIPTables" fixedAssetId="Private Rooms" nrOfSpaces="1" accomNumber="PrVIP-5"/>
Now you can book spots 9,10,11 if you want 3 spaces and not 4 or just spot 12 if you want 1 space only.
Well, this is not the best way for me....(too many records and not a real solution)

2) Think of making the tables fa related to their rooms with parent-child relationship. Then define new accommodationClass somethng like:
30_place_table and relate it to the table in the MAP. Then think of using no assign algorithm using this map record, which means you will not reserve spots anymore but products related to a given class. Of course this sort of algorithm would require more extentions of our CURRENT model so I am not sure if it will be the best for you...

Regards:Valentina

[Karnchana]

Hi, Valentina  thanks for your answer

but in :

<AccommodationSpot accommSpotId="8" accommClassId="VIPTables" fixedAssetId="Private Rooms" nrOfSpaces="4" accomNumber="PrVIP-5"/> 


I meant VIPTables in Private Rooms and 4 (people) capacity with name PrVIP-5. It's one table.


Regards,
Karnchana

[hans_bakker]

Hi Valentina,

thanks for your input. The first thing we have to agree now is the
entity definitions. Then later we can see how we do the availability
checking. For the good order: Karnchana is working with me here.

here was you original proposal from you which is a little modified by
me:
1. on the accomodation spot it is perhaps better to use the
'description' field instead of number, to be able to also say: "table in
the corner with the nice view"? It still can contain a number.
2. In the accomodation map i do not see the reason for the 'overbooked'
field, availability is better kept the the workeffort entity (search the
workeffort entity for the specific time and the specific accomodation
map/spot) and the number of spaces is available in the accomodation map.
3. should the fixed asset be part of the primaryKey of the accomodation
map? let me know why?
4. Should the class be a field in the map? isn't it more a specification
of the spot?
5. I did some adjustments to the field types.

please let me know what you, and others think about this proposal.

    <entity entity-name="AccommodationClass"
            package-name="org.ofbiz.order.reservations"
            title="Accommodation Class">
        <field name="accommClassId" type="id-ne"></field>
        <field name="parentAccommClassId" type="id"></field>
        <field name="description" type="description"></field>
        <prim-key field="accommClassId"/>
        <relation type="one" fk-name="ACCOMM_CLASS_PAR" title="Parent"
  rel-entity-name="AccommodationClass">
            <key-map field-name="parentAccommClassId"
         rel-field-name="accommClassId"/>
        </relation>
    </entity>
   
    <entity entity-name="AccommodationSpot"
            package-name="org.ofbiz.order.reservations"
            title="Accommodation Spot">
        <field name="accommSpotId" type="id-ne"></field>
        <field name="accommClassId" type="id"></field>
        <field name="fixedAssetId" type="id"></field>
        <field name="nrOfSpaces" type="numeric"></field>
        <field name="description" type="description"></field>
        <prim-key field="accommSpotId"/>
        <relation type="one" fk-name="ACCOM_CLASS"
         rel-entity-name="AccommodationClass">
            <key-map field-name="accommClassId"/>
        </relation>
        <relation type="one" fk-name="SPOT_FA"
         rel-entity-name="FixedAsset">
            <key-map field-name="fixedAssetId"/>
        </relation>
        <relation type="one-nofk" fk-name="ACCOMM_MAP"
         rel-entity-name="AccommodationMap">
            <key-map field-name="accommClassId"/>
            <key-map field-name="fixedAssetId"/>
        </relation>
    </entity>
   
    <entity entity-name="AccommodationMap"
            package-name="org.ofbiz.order.reservations"
            title="Accommodation Map">
        <field name="accommMapId" type="id-ne"></field>
        <field name="fixedAssetId" type="id"></field>
        <field name="nrOfSpaces" type="numeric"></field>
        <prim-key field="accommMapId"/>
        <relation type="one" fk-name="ACMD_MAP_FA"
         rel-entity-name="FixedAsset">
            <key-map field-name="fixedAssetId"/>
        </relation>
    </entity>

Add 2 fields to WorkEffort and to the shoppingcart:

        <field name="accommMapId" type="id"/>
        <field name="accommSpotId" type="id"/>

--
AntWebsystems.com: Quality OFBiz services for competitive rates.....

[David E Jones]

For those watching on the user list only: I've replied to this on the  
dev list, as it's really a discussion of OFBiz enhancements.

-David


On Jan 27, 2008, at 8:58 PM, Hans Bakker wrote:

> Hi Valentina,
>
> thanks for your input. The first thing we have to agree now is the
> entity definitions. Then later we can see how we do the availability
> checking. For the good order: Karnchana is working with me here.
>
> here was you original proposal from you which is a little modified by
> me:
> 1. on the accomodation spot it is perhaps better to use the
> 'description' field instead of number, to be able to also say:  
> "table in
> the corner with the nice view"? It still can contain a number.
> 2. In the accomodation map i do not see the reason for the  
> 'overbooked'
> field, availability is better kept the the workeffort entity (search  
> the
> workeffort entity for the specific time and the specific accomodation
> map/spot) and the number of spaces is available in the accomodation  
> map.
> 3. should the fixed asset be part of the primaryKey of the  
> accomodation
> map? let me know why?
> 4. Should the class be a field in the map? isn't it more a  
> specification
> of the spot?
> 5. I did some adjustments to the field types.
>
> please let me know what you, and others think about this proposal.
>
>    <entity entity-name="AccommodationClass"
>            package-name="org.ofbiz.order.reservations"
>            title="Accommodation Class">
>        <field name="accommClassId" type="id-ne"></field>
>        <field name="parentAccommClassId" type="id"></field>
>        <field name="description" type="description"></field>
>        <prim-key field="accommClassId"/>
>        <relation type="one" fk-name="ACCOMM_CLASS_PAR" title="Parent"
> rel-entity-name="AccommodationClass">
>            <key-map field-name="parentAccommClassId"
> rel-field-name="accommClassId"/>
>        </relation>
>    </entity>
>
>    <entity entity-name="AccommodationSpot"
>            package-name="org.ofbiz.order.reservations"
>            title="Accommodation Spot">
>        <field name="accommSpotId" type="id-ne"></field>
>        <field name="accommClassId" type="id"></field>
>        <field name="fixedAssetId" type="id"></field>
>        <field name="nrOfSpaces" type="numeric"></field>
>        <field name="description" type="description"></field>
>        <prim-key field="accommSpotId"/>
>        <relation type="one" fk-name="ACCOM_CLASS"
> rel-entity-name="AccommodationClass">
>            <key-map field-name="accommClassId"/>
>        </relation>
>        <relation type="one" fk-name="SPOT_FA"
> rel-entity-name="FixedAsset">
>            <key-map field-name="fixedAssetId"/>
>        </relation>
>        <relation type="one-nofk" fk-name="ACCOMM_MAP"
> rel-entity-name="AccommodationMap">
>            <key-map field-name="accommClassId"/>
>            <key-map field-name="fixedAssetId"/>
>        </relation>
>    </entity>
>
>    <entity entity-name="AccommodationMap"
>            package-name="org.ofbiz.order.reservations"
>            title="Accommodation Map">
>        <field name="accommMapId" type="id-ne"></field>
>        <field name="fixedAssetId" type="id"></field>
>        <field name="nrOfSpaces" type="numeric"></field>
>        <prim-key field="accommMapId"/>
>        <relation type="one" fk-name="ACMD_MAP_FA"
> rel-entity-name="FixedAsset">
>            <key-map field-name="fixedAssetId"/>
>        </relation>
>    </entity>
>
> Add 2 fields to WorkEffort and to the shoppingcart:
>
>        <field name="accommMapId" type="id"/>
>        <field name="accommSpotId" type="id"/>
>
> --
> AntWebsystems.com: Quality OFBiz services for competitive rates.....
>

[Valentina Sirkova]

Hi Hans and others,
I will start from discussing Hans post and then comment and answer to
the rest of the posts (they are a lot :) ).

On Mon, 2008-01-28 at 10:58 +0700, Hans Bakker wrote:

> 1. on the accomodation spot it is perhaps better to use the
> 'description' field instead of number, to be able to also say: "table in
> the corner with the nice view"? It still can contain a number.
Yes Hans, I agree here but maybe it will be even better if we still KEEP
this field as it is designed to store accommodation number and not the
description. Maybe we could simply add one more field for description.
> 2. In the accomodation map i do not see the reason for the 'overbooked'
> field, availability is better kept the the workeffort entity (search the
> workeffort entity for the specific time and the specific accomodation
> map/spot) and the number of spaces is available in the accomodation map.
Well, I put overbooked field there as this entity`s purpose is to keep
the general data for the class, so I thought its logical place is there.
Also these 3 entities are part of "travel orders" from the book which do
not involve the Workeffort entity at all. I know the CURRENT OFBiz
implementation involves it in the reservation logic and therefore the
easiest way is to put it there..but I am not sure if extending the
workeffort entity is the real solution, it is probably sort of a FIX
with our current logic.
IMHO workeffort and techdata calendars should not be involved with
reservations and NEW improved implementation of the reservations in
OFBiz should be introduced based on the book-I can help with that.
Actually there was discussion based on that which died
http://www.nabble.com/Stop-using-techdata-calendar-to12220306.html#a12220306 

> 3. should the fixed asset be part of the primaryKey of the accomodation
> map? let me know why?
Well, it makes sense to me why the book models it this way -
I guess the reason for that is as all classes are related to some
fas(hotels,restaurants etc) but I do not believe this should be a must
for us!
> 4. Should the class be a field in the map? isn't it more a specification
> of the spot?
YES, definately..there is no point of the map without the class... It
should say how many places a class has in a given FIXEDASSET.

I guess the question comes from the fact that these two entities are
almost the same! Well, I was with this confusion for a while too. These
two entities have really similar fields so for some time on I was using
the map instead of the spot in my local copy and that is why I described
to you the number of the seats etc in the map entity:)  see here
<a href="http://www.nabble.com/Re%3A-seats-tables-in-a-restaurant-%">http://www.nabble.com/Re%3A-seats-tables-in-a-restaurant-%
28or-plane-or-cinema%29-p14891741.html

Here is a quote from the book p370 (I guess I am answering
David`s question here)
"Because Transportation vehicles and hotels have AccommodationMaps to
show what can be booked figure 8.3 each ReservationItem may be reserving
an AccommodationSpot which would reserve a specific SEAT Number or Room
number whihch would correspond to a spot that is available withiin the
AccommodationMap for that type of accommodation"

I guess this separation is logical. Map entity saying only the total
number of spaces per class and spot entity - the actual spot(seat,room)
within this class. Which means availability services would check the
total capacity of the the room type in the map entity and then would
reserve spots (real room numbers) from the spot entity.

Well this could be achieved with one entity only - the spot entity.
The cons of having one entity is it will be larger and this logical
distinciton- map and the specific spot within that map will not be that
intense.

> 5. I did some adjustments to the field types.
>
> please let me know what you, and others think about this proposal.
>
>     <entity entity-name="AccommodationClass"
>             package-name="org.ofbiz.order.reservations"
>             title="Accommodation Class">
>         <field name="accommClassId" type="id-ne"></field>
>         <field name="parentAccommClassId" type="id"></field>
>         <field name="description" type="description"></field>
>         <prim-key field="accommClassId"/>
>         <relation type="one" fk-name="ACCOMM_CLASS_PAR" title="Parent"
>   rel-entity-name="AccommodationClass">
>             <key-map field-name="parentAccommClassId"
> rel-field-name="accommClassId"/>
>         </relation>
>     </entity>
>    
>     <entity entity-name="AccommodationSpot"
>             package-name="org.ofbiz.order.reservations"
>             title="Accommodation Spot">
>         <field name="accommSpotId" type="id-ne"></field>
>         <field name="accommClassId" type="id"></field>
>         <field name="fixedAssetId" type="id"></field>
>         <field name="nrOfSpaces" type="numeric"></field>
>         <field name="description" type="description"></field>
>         <prim-key field="accommSpotId"/>
>         <relation type="one" fk-name="ACCOM_CLASS"
> rel-entity-name="AccommodationClass">
>             <key-map field-name="accommClassId"/>
>         </relation>
>         <relation type="one" fk-name="SPOT_FA"
> rel-entity-name="FixedAsset">
>             <key-map field-name="fixedAssetId"/>
>         </relation>
>         <relation type="one-nofk" fk-name="ACCOMM_MAP"
> rel-entity-name="AccommodationMap">
>             <key-map field-name="accommClassId"/>
>             <key-map field-name="fixedAssetId"/>
>         </relation>
>     </entity>

(just a note here)
The last relation to the Map entity..if you get rid of the class field
in the map entity as you have showed in your post you cant use the last
relation to AccommodationMap like this.Maybe something like this would
be right:
        <relation type="one-nofk" fk-name="ACCOMM_MAP"
         rel-entity-name="AccommodationMap">
            <key-map field-name="fixedAssetId"/>
        </relation>

>     <entity entity-name="AccommodationMap"
>             package-name="org.ofbiz.order.reservations"
>             title="Accommodation Map">
>         <field name="accommMapId" type="id-ne"></field>
>         <field name="fixedAssetId" type="id"></field>
>         <field name="nrOfSpaces" type="numeric"></field>
>         <prim-key field="accommMapId"/>
>         <relation type="one" fk-name="ACMD_MAP_FA"
> rel-entity-name="FixedAsset">
>             <key-map field-name="fixedAssetId"/>
>         </relation>
>     </entity>

I do not believe this is a good approach.

>
> Add 2 fields to WorkEffort and to the shoppingcart:
>
>         <field name="accommMapId" type="id"/>
>         <field name="accommSpotId" type="id"/>


IMHO, this way of thinking would lead to many more
extentions to an entity which is probably more intented for
manufacturing and workeffort usage and not for reservations.
For example sooner or later we`ll need to add as David suggested
ScheduledTransportation entities, also I believe we`ll need to add
Ticket entitiy and maybe others which would relate to the
ReservationItem directly. This`ll lead to further extentions to
Workeffort.

The book handles reservations thru Reservation and Reservation entities.
In our OFBiz project this is neither the Workeffort nor any of the
TechDataCalendars and I am not sure if complicating them further is a
good way to go.

I guess the better direction would be to rethink our current OFBiz
implementation and improve it based on the book`s way. IMO,
OrderItem is logically what is ReservationItem from the book and
this is the entity which will have to be extended(but I need the
comminity`s opinion on that). Of course this
would mean we`ll have to reimplement our current OFBiz code. I have
ideas how this could happen and what exactly should be changed. If the
community is interested in this idea we could discuss it further and
I could provide more detailed picture of that.

[Valentina Sirkova]

Karnchana wrote

Hi, Valentina  thanks for your answer

but in :

<AccommodationSpot accommSpotId="8" accommClassId="VIPTables" fixedAssetId="Private Rooms" nrOfSpaces="4" accomNumber="PrVIP-5"/> 


I meant VIPTables in Private Rooms and 4 (people) capacity with name PrVIP-5. It's one table.


Regards,
Karnchana

Yes, Karnchana that is how I understood it and what I ment. I guess I didn`t express myself very well :)

Valentina