Database fields advice

markeh · 18 Sep 2008 at 13:51

Hi there need some advice.

I have a table of site locations in a mySQL database which has 20 fields, the usual ID, address, facilities and the like.

I need to add a dates/prices grid to the information the table contains which is something like:

Date | Weekly Rate | Weekend Rate | Daily Rate | Offers
14th-18th July | £123 | £35 | £10 | somestring
19th-25th July | £132 | £53 | £15 | somestring

etc

There will be a limit of 11 rows of dates, so combined that would be an extra 55 fields added to the total, which seems excessive.

I thought about having each row in a string comma seperated reducing the fields to 11, would that be more efficient that having the extra 44 fields? Any other way of doing this?

Cheers

SimonCHere · 18 Sep 2008 at 14:07

Try doing it properly?

From what you've described your should have at least 5 tables for the data.

Site (ID, description)
Address (SiteID, line1, line2, line3, line4, pcode)
SiteFacilities (SiteID, FacilityID)
Facility (ID, description)
Rates (SiteID, dateFrom, dateTo, weeklyRate, WeekendRate, DailyRate, Offers)

Simon

markeh · 18 Sep 2008 at 14:28

Wasn't me that made the table :cool:

I suppose I could create a new table for the dates/rates for this, does seem to make sense, thanks.

SimonCHere · 18 Sep 2008 at 14:36

Fair enough!

Create a rates table then and use the ID from the other table to link it.

Good luck!

markeh · 19 Sep 2008 at 12:49

Is it possible using one SQL statement to pull out one value from the 1st site locations table and then all of the matching dates and prices in the 2nd table for that location? I tried playing around a bit with joins, but don't know how to get it to get the multiple matching rows from the 2nd table.

GravyMonster · 19 Sep 2008 at 13:39

an inner join is what you need - if you google for it there will be lots of examples.

Without knowing your table schema I couldn't tell you exactly what you need, but it will be along the lines of...

Code:

SELECT * FROM Table1 INNER JOIN Table2 on Table1.PrimaryKey = Table2.ForeignKey

markeh · 19 Sep 2008 at 14:01

I have tried inner joins but don't know how to get it to do what I want (I have googled this as well and can't find an example that seems to match)

Imagine Table1 has:

siteid | name | address
1 | London House | London Road, Londonshire
2 | Cambridge House | Cambridge Road, Cambridgeshire
3 | Birmingham House | Birmingham Road, Birminghamshire
4 | Liverpool House | Liverpool Road, Liverpoolshire

Table2:

id | siteid | date | week_price | day_price
1 | 1 | 1st july | £390 | £75
2 | 1 | 8th July | £350 | £95
3 | 1 | 15th July | £340 | £85
4 | 2 | 1st july | £290 | £55
5 | 2 | 8th July | £250 | £75
6 | 2 | 15th July | £240 | £65
7 | 3 | 1st july | £340 | £55
8 | 3 | 8th July | £290 | £45
9 | 3 | 15th July | £230 | £35
10 | 4 | 1st july | £240 | £25
11 | 4 | 8th July | £190 | £35
12 | 4 | 15th July | £130 | £15

What I want to appear on page.asp

Name: London House

Address: London Road, Londonshire

Prices:
1st july
Weekly rate: £390
Daily rate: £75

8th July
Weekly rate: £350
Daily rate: £95

15th July
Weekly rate:£340
Daily rate: £85

From what I've managed to do with inner joins all I could get out is (without formatting):

1 | London House | London Road, Londonshire | 1 | 1 | 1st july | £390 | £75
1 | London House | London Road, Londonshire | 2 | 1 | 8th July | £350 | £95
1 | London House | London Road, Londonshire | 3 | 1 | 15th July | £340 | £85

I could use somekind of nested SQL statements but I assume there is a better way. :confused:

GravyMonster · 19 Sep 2008 at 14:35

Well, if that's what your query is returning, thats correct.

In your ASP you just need display the data about the house, and after that loop around the recordset and display each week's costs.

If you're still stuck let me know and I'll give you some ASP code which should help.

SimonCHere · 19 Sep 2008 at 14:37

Isn't that what you want though?
The web page then steps through each record in the recordset writing out

Code:

<%
while not rs.eof
if counter=1 then
response.write("Name: " & rs("Name") & "<BR>")
response.write("Address: " & rs("Address") & "<BR>")
end if
%>
<%= rs("date") %>
Weekly Rate: <%= rs("wrate") %>
Daily Rate: <%= rs("drate") %>

counter ++
rs.movenext
wend

^^ code garenteed not to work!

If you want to show all the locations with all their times you have to change the counter check to check for a change in the name (or siteid)
so:

Code:

if rs("siteid") <> lastID then

write name and address

lastID = rs("siteid")
end if

Simon

markeh · 19 Sep 2008 at 16:13

Cheers guys.

Code:

<%
while not rs.eof
if counter=1 then
response.write("Name: " & rs("Name") & "<BR>")
response.write("Address: " & rs("Address") & "<BR>")
end if
%>
<%= rs("date") %>
Weekly Rate: <%= rs("wrate") %>
Daily Rate: <%= rs("drate") %>

counter ++
rs.movenext
wend

Is the other way I considered doing it, I just wondered if it was possible to do the same kinda thing but in SQL somehow.

SimonCHere · 19 Sep 2008 at 16:47

If it was .NET you could bring back a dataset with 2 tables in it and join them on the ID then use that, personally I would do it the above way though!

Simon