Data modeling : gas station managing

Question

I need to create a database to manage a gas station.

I'm thinking of a basic product inventory and sales data model, but it need some changes.

See http://www.databaseanswers.org/data_models/inventory_and_sales/index.htm. This is how they proceed: the manager keep tracks of the inventory and sales twice a day, each time a gas pump attendant is in charge, and takes the responsibility of the sales.

How can I keep track of this ?

Do you record sales only for gas/fuel? Or you have also other products let's say lubricant? — Edper, Apr 26 '13 at 01:02
thank u for responding ,i record fuel and lubricant but that's not a problem , my concern is how to handle the two times a day inventory. — user1656898, Apr 26 '13 at 01:49

score 1 · Accepted Answer · answered Apr 26 '13 at 02:36

Using the Model that you provided you could use the first Model as reference:

And I would use all the six (6) tables namely:

 1) Products
 2) Product_Types
 3) Product_In_Sales
 4) Sales
 5) Daily_Inventory_Level
 6) Ref_Calendar

But I had to make some changes by alteration and adding:

First I need to include SalesPerson table that would have at least the following fields

 1) SalesPersonID
 2) Lastname
 3) Firstname
 4) Alias

In line with that I therefore need to add SalesPersonID as Foreign key in my Sales table.

Now since you want to have twice a day Inventory then you could approach in many ways you could add single primary key for Daily_Inventory_Level table or you could add a new field named Inventory_Daily_Flag which has either only the value of 1 or 2. If 1 that means that's the first inventory and if 2 that means that's the second inventory for the day. And that means by the way that you're Primary and Foreign Key at the same time would no longer be just Day_Date and ProductID but also Inventory_Daily_Flag for Daily_Inventory_Level table.

And also in line with that, that means you need to also to add a field in your Product_In_Sales like FlagForInventory with Boolean as Data Type.

So, let's say a Supervisor came in to do the first inventory, then the products sold in Product_In_Sales for the day would be flag as True for the FlagForInventory and then would be transferred to Daily_Inventory_Levels with Inventory_Daily_Flag field as 1 to indicate as the first inventory and of course the Level also would be updated.

And so when the days end and the 2nd inventory is to be executed then those sales for the day from Product_In_Sales table whose FlagForInventory is false then it would be flag as True for FlagForInventory and then transferred again to Daily_Inventory_Levels with Inventory_Daily_Flag as 2 indicating the second inventory. And of course you need to update the Level as well.

Does it make sense? If not I could always change the approach? ;-)

thank u a lot for taking the time to explain you're approach,u just helped me keep going ; good bless you . — user1656898, Apr 26 '13 at 03:54

Data modeling : gas station managing

1 Answers1