database desing help

[zxmax]

Hi have static page on a web site, that i would like to change it to dynamic so user can update it.
That page has about 7 different tables with their own headers .. here is the sample

http://www.pcclick.ca/test/margins.htm

what is the best way of creating my database (i'm using MS access) would it be best to create 7 different tables , 1 for each table, or one table ?

i'm a rookie at this , i just want to know how would you guys do it ..

Thanks in advance

 

[webmigit]

2 tables

First table contains all your types...

TypeID TypeName
1      Agricultural
2      Financial
3      Softs
4      Currencies
5      Livestock
6      Energies
7      Metals

(I abbreviated some)

Second table
ItemID Item               InitMargin MaintMargin TypeID
1      Corn               540        400         1
2      Oats               675        500         1
...
8      DJ IA(sm)-$10.00   5400       4000        2
9      Sugar              700        500         3
10     CRB Index          1500       1500        2

and so on and so forth

You'll notice how the typeIDs for each item match the TypeID for each type..

You'll also notice in the second table how they're not exactly in order (1,1,2,3,2).. That's just to show that they don't have to be in any particular order.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[zxmax]

Thank you webmigit , that is great help ,
What i've done is i created 7 different recordset with this "where" statement, to differentiat between the tables,

where Items.TpyeID like 1 >> for the agriculture table
where Items.TpyeID like 2 >> for the Financial table
.
.
.
etc,

Is that good approch, or that take too much of the server resources ?

Again thanks for the help

 

[imstillatwork]

I would use one query, and I'll show ya, 'cause I'm nice.
You can add formating as you like.
Using webmigits db design->

<cfquery name="qGetData" datasource="yourdsn">
	SELECT 
		I.ItemID
		,I.Item
		,I.InitMargin
		,I.MaintMargin
		,I.TypeID

		,T.Typename
		,T.TypeID

	FROM Items AS I
	LEFT JOIN Types AS T
	ON T.TypeID=I.TypeID
		
</cfquery>

<cfoutput query="yourqueryname" group="typeID">
	<table>
		<tr>
			<td>#typeName#</td>
			<td>Initial Margin (per contract)</td>
			<td>Maintenance Margin (per contract)</td>
		</tr>
		<cfoutput>
			<tr>
				<td>#Item#</td>
				<td>#InitMargin #</td>
				<td>#MaintMargin#</td>
			</tr>
		</cfoutput>
	</table>
        <br><br>	
</cfoutput>

 

[zxmax]

Thnak you both for the assisstance, that is just a great help

 

[imstillatwork]

on my example above, does it matter what table you select from first? items or type?