i need to normalize a database and create a search/sort
i have a db of owners to their boat. i need this database analyzed to
see if its ok but mainly need a sortability that will display all the
boat info in a repeat region and on that same page have dropdowns
along the top that will allow sorting by Owner OR Designer OR Build
Date OR Owners Name OR Boats Name OR Country. on another page the
members info needs to be displayed with all boats that they own. what
is messing me up is that boats can have more than one owner and owners
can own more than one boat. i am not familar with doing many to many
relationship searches.
i need code for this and advice! thanks!
http://www.binarian.com/search.mdb |
Request for Question Clarification by
mathtalk-ga
on
12 Nov 2003 13:56 PST
Hi, binarian-ga:
I'm not sure what sort of "code" you need. Maybe some SQL for the
queries that demonstrates sort orders? First some advice about
database design.
It can be awkward to normalize a many-to-many relationship. Typically
the approach would be to use three tables, one which lists all the
boats exactly once, another which lists all boat owners exactly once,
and then a third table which represents the many-to-many relationship,
all boat-owner pairs where this boat is at least partly owned by this
owner.
Details specific to a boat, such as boat name, designer, build date,
registration, etc. would be kept in the first table, and details
specific to an owner, such as owner's name and address, would be kept
in the second table.
The Boat and Owner tables might lend themselves to the use of
artificial primary keys, like an "identity" column (autoincrement in
Access), for a unique identifier. Whatever are used as the unique
identifiers in these two tables would be foreign keys in the third
table, e.g. BoatOwner, where the combination of the two foreign keys
would make up a composite primary key (and these would be the only two
fields in the BoatOwner table unless you have some additional
information that can be stored there, like percent ownership).
best wishes, mathtalk-ga
|
Clarification of Question by
binarian-ga
on
13 Nov 2003 06:30 PST
did u take a look at the database that i had linked up...i think i was
on the right track then. whati need is code.. in asp/sql that will
allow this database informtion to be sorted on the search page as i
indicated.
by Owner OR Designer OR Build
Date OR Owners Name OR Boats Name OR Country.
|
Request for Question Clarification by
mathtalk-ga
on
14 Nov 2003 13:01 PST
Hi, binarian-ga:
Thanks for the Clarification. I'd assumed from the Subject that the
question was focused on the database/SQL side of things, but perhaps
the real issues are with ASP coding techniques.
So, let me ask if you want ASP vs. ASP.Net, and also confirm that the
desired functionality of this information is display only, ie. you're
not trying to create an editable screen of data.
thanks, mathtalk-ga
|
Clarification of Question by
binarian-ga
on
17 Nov 2003 10:02 PST
i will at some point need to edit, add and delete these areas.. but
right now i would like to get it working .. the search page itself.
ASP is the way.. no .NET yet : )
|
Clarification of Question by
binarian-ga
on
24 Nov 2003 11:20 PST
where do we need to go from here?
|
Request for Question Clarification by
mathtalk-ga
on
24 Nov 2003 13:33 PST
Let me put together a quick sample page and you can kind of see how
well (or poorly!) that kind of help will address your design
questions.
The SQL part is pretty straightfoward, I can give you queries that do
the sorts in the order specified, but where I initially assumed that's
the question you had, it seems the thread has evolved more along the
lines of how do I do this so it shows up in an ASP page.
Let me know if I've wandered off the beaten path here.
regards, mathtalk-ga
|
Clarification of Question by
binarian-ga
on
03 Dec 2003 14:01 PST
yer on track...please proceed!
|
Clarification of Question by
binarian-ga
on
04 Dec 2003 13:09 PST
btw the column headings are messed up.. they have spaces and :
characters in the headings...
|
