May 3, 2002 at 5:35 am
Dear Friends,
I am developing a package for a fishing company.It has 10 Vessels which go around for fishing.Each of these vessels go for around 45 Days Voyage in the sea.Each contain a Captain,Engineer and crew members.Each vessel catches the same kind of Fish or Prawns,Hence items caught in all the vessels are the same , Ofcourse the quantities varies according to the location ,where they are fishing , season etc.The management wants to keep track of the catch on a daily basis , voyage basis ,captain wise,season wise,location wise etc.Each of these items are associated with a rate in local currency for fish and US Dollar for Prawns.This is because Fish is Locally sold and Prawns are being exported. At present the management is monitoring the daily catch value each vessel is catching.The catch details are taken everyday morning from the sea-coast. Sometimes the captain or engineer are changed due to some technical or personal needs.These things are also to be tracked along with the date .
My concern is how should I design the tables.I have something of this sort in my mind.
How should take the input of the catch on a daily basis.
1>Shall I create a single table with the following Fields
Item_Rowid,Vessel_One,Vessel_Two……Vessel_Ten
And program in such a way to capture into this table.
2>Or Shall I create a separate table for each vessel in such a way.
Item_Rowid,Item_Catch
I am Totally confused please guide me
Regds
Prakash
May 3, 2002 at 6:08 am
SET NOCOUNT ON
SELECT * FROM xtext
TRUNCATE TABLE xtext (
[user_id] [numeric](8, 0) NULL ,
[f1] [varchar] (100) NULL ,
[f2] [varchar] (100) NULL ,
[f3] [varchar] (100) NULL ,
[f4] [varchar] (100) NULL ,
[f5] [varchar] (100) NULL
) ON [PRIMARY]
GO
INSERT INTO xtext ([user_id],f1,f2,f3,f4,f5) VALUES(1, 'good', 'bad', 'o.k.', 'bad', 'good')
INSERT INTO xtext ([user_id],f1,f2,f3,f4,f5) VALUES(1, 'bad', 'good', 'normal', 'good', 'bad')
INSERT INTO xtext ([user_id],f1,f2,f3,f4,f5) VALUES(1, 'excellent', 'bad', 'o.k.', 'bad', 'good')
INSERT INTO xtext ([user_id],f1,f2,f3,f4,f5) VALUES(2, 'good', 'bad', 'o.k.', 'bad', 'good')
INSERT INTO xtext ([user_id],f1,f2,f3,f4,f5) VALUES(2, 'excellent', 'bad', 'o.k.', 'bad', 'good')
INSERT INTO xtext ([user_id],f1,f2,f3,f4,f5) VALUES(2, 'normal', 'bad', 'o.k.', 'bad', 'good')
GO
--------------------The code that does the work----------------------------
For you explination of the example given I would have a table with the vessels with a unique id for the vessel. Then I would create the daily catch table like so
VesselUNIID,
CatchDate,
CatchCount
Thsi way you can quickly pinpoint a day or a vessel and the vessel table can be used to link to a crew table and reistration info can be in the vessel table. Consider the vessel as an element, the catch as one, the crew as one, the crews details as one, and maybe you catch want to take into account type of fish in individual accoutns it will be easy to scale that in. The key is IMHO that you consider future needs and make sure you develope to be able to scale the DB.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 3, 2002 at 6:18 am
But Each Vessel catches around fifty items on a daily basis.Also the catch is reported in quantities (like 30 Kg or 30 bags etcc) but not in terms of quality
May 3, 2002 at 6:52 am
So think about the break down and how they logically apply to each other and build tables that express it out. Then normalize by thinking about where items are used repeatedly with common factors and split table that way, then try again. For instance to deal with the amount and basis you could have a column for CatchAmt and one for CatchBasis which could be a select of kg, bags, etc. Write out logicall what you need to express and see then post here and we can suggest based on what you are thinking.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply