Curious as to the "correct" way

[Lucio]

Ok, on our company website we've recently asked the developers to add a more effective search engine to cover our range of keys. The site is written in PHP and uses MySQL to store the data

Each key has several different attributes that a customer might want to use to search on, split into two different searchs, such as what type it is, what it's made from, overall size and so on. The solution we've been given for this looks to be ok, as it stores a table with catagories and then in another table there's a list of what each catagory can contain.

So far so good I thought?


The second part of the search is the one I think is generating the problem. For car keys, we need to store the Make, Model and Year range that each particular key works and each key can have more than one vehicle or year range it works on (personally I wanted a start date and an end date but aparently that's too hard for them)

Now, the way they've stored this information is three tables, one storing Make, one storing model (which references the Make) and one storing the Year (which references the Make table) and then the search looks up entries in the Year table. Now to me, this seems completely arse about face, but I can't seem to get the website developers to understand this. More importantly, the search page simply fails to work if you don't have at least one complete entry in the Vehicle listing (e.g. a Make with a Model Underneath it and a Year underneath the model).


What I'm looking for is some basic advice on how you'd solve this problem, because I really don't have any faith in the design company to solve this and I'd like to offer a more proactive solution than simply going "It's broke, I've checked and double checked the data going in and that's ok"

[aidanjt]

This is how I'd do it.

table: Cars
int carid (primary key)
string model
int make (reference to Manufacturers.makeid)
int year (reference to Year.makeid)

table: Manufacturers
int makeid (key)
string make

table: Year
int yearid (key)
int year

Then, when you're looking for a list of cars in a given year, with SQL queries, such as "SELECT yearid FROM Year WHERE year==$year", then use that to query the Cars table with "SELECT * FROM Cars where yearid=$yearid", and so on, so forth. You could expand that last query to return a more relevant resultset, so to PHP the data will be:
int carid
string model
string make
int year

etc..

[TheAnimus]

this is the joy of using ideas from 20 years ago.

you've got a database, that sounds as though its a million miles away from 3rd normal form.

for the search they want to build some kind of index layer over the top, which also dictates the options, but then u've got all kinds of concurrency issues?

now if only there where technologies like http://weblogs.asp.net/scottgu/archi...nslations.aspx or http://www.nhibernate.org/
where you get used to simply forgetting about such trival stuff as how your data is stored.

you can simply go

CarBookings.Unique(x=>x.Model) to get your available models etc.

Moving the database to 3rd normal form would break everything. Why not do it properly and move it to a technology of this century?

[aidanjt]

this is the joy of using ideas from 20 years ago.

you've got a database, that sounds as though its a million miles away from 3rd normal form.

for the search they want to build some kind of index layer over the top, which also dictates the options, but then u've got all kinds of concurrency issues?

now if only there where technologies like http://weblogs.asp.net/scottgu/archi...nslations.aspx or http://www.nhibernate.org/
where you get used to simply forgetting about such trival stuff as how your data is stored.

you can simply go

CarBookings.Unique(x=>x.Model) to get your available models etc.

Moving the database to 3rd normal form would break everything. Why not do it properly and move it to a technology of this century?

What?

Just because something is old and lost it's hyperbole factor (many) years ago, it doesn't mean it went bad or that newer variants are superior. Usually it's the opposite. And frankly, not caring about how your data is structured is lazy to the point of negligence.

Also your post was completely unhelpful to the OP.

[TheAnimus]

If you fail to leverage new technology that makes it quicker and easier to develop stuff, thats a cost.

My point is, it sounded like they are fighting against a lack of a schema?

Why not look at an evolved way of doing it?

Play with somehting like Linq to Sql, and tell me it isn't better to be data store agnostic, espesually when speccing out something like this!

[yamangman]

Not too sure how you reckon LINQ to SQL is data store agnostic, when it runs against SQL Server only, and how you can recommend it, when it is going to be dead and redundant in a few months.

[Lucio]

If you fail to leverage new technology that makes it quicker and easier to develop stuff, thats a cost.

My point is, it sounded like they are fighting against a lack of a schema?

Why not look at an evolved way of doing it?

Play with somehting like Linq to Sql, and tell me it isn't better to be data store agnostic, espesually when speccing out something like this!

To be fair to TheAnimus, I asked for the "correct" way to handle a task like this, not how on earth I'm going to get the web developers to make it work given the current limitations (LAMP setup)


At the end of the day, it confirms my suspicions that the Sales Manager hasn't given enough thought to the project, and instead has focused on the surface things like "is it pretty". It's bad enough that I'm having to convert a tonne of data from our SOP system that runs on FoxPro and comes out via Excel into web ready information, but to then discover after doing all the work that in fact the system can't handle it and was tested with "a couple of examples and worked fine"


WTB: Real IT job please!

[TheAnimus]

Not too sure how you reckon LINQ to SQL is data store agnostic, when it runs against SQL Server only, and how you can recommend it, when it is going to be dead and redundant in a few months.

If you've written with Linq To SQL, what difficulty to you have porting to any other LINQ provider? pratically nothing!

I still like LINQ to SQL as an incredibly easy to learn system. It's stupidly simply to get to grips with.

The idea is thats its only the Objects that matter, and you don't give a toss about how its stored. For so many things you dont have to worry (ok, part of my day job includes interacting with a bespoke tick database, i wouldn't begin to sugest you could ignore something like that!) without any thought.

So, my point is if you've designed against linq to sql with your code, how hard is it to move to nHibernate or whatever the flavour of the month is?

Ps linq to sql isn't going to be dead, just not have new features added, its still a great example!

And Apologies to Lucio, i took this more as a "how would i ensure i'm never in this situation, ever" type question.

[directhex]

Not too sure how you reckon LINQ to SQL is data store agnostic, when it runs against SQL Server only, and how you can recommend it, when it is going to be dead and redundant in a few months.

http://code.google.com/p/dblinq2007/

[yamangman]

http://code.google.com/p/dblinq2007/

dblinq is pretty broken.