2

I'm currently planning the database structure of a used car's advertisements site. Each advert contains information about a vehicle, and a vehicle can be advertised multiple times over its lifespan (granted, quite unlikely). I'm unsure which approach to take - a single table, or two separate relational tables, each has their pros and cons.

Schema 1

Adverts Table:
-------------------
id            int
make          varchar(255),
model         varchar(255),
year          int,
transmission  varchar(255),
fuel_type     varchar(255),
body_type     varchar(255),
engine_size   int,
colour        varchar(255),
doors         int,
location      varchar(255),
price         int,
owners        int,
mileage       int,
description   longtext

Schema 2

Adverts Table
-------------------
id            int
location      varchar(255),
price         int,
owners        int,
mileage       int,
description   longtext
vehicle_id    int

Vehicles Table
-------------------
id            int
make          varchar(255),
model         varchar(255),
year          int,
transmission  varchar(255),
fuel_type     varchar(255),
body_type     varchar(255),
engine_size   int,
colour        varchar(255),
doors         int

Personally, I'm thinking taking the second approach will benefit in the future, but it's proving tricky to work with. I'm using Laravel, a PHP framework. Because most of the "search" options are applied to the vehicle (parent), rather than the advert (child), a lot more code is required, which is starting to seem counter-intuitive. Schema 1 is far simpler to work.

Am I missing any extensive benefits of going with schema 2? In a nutshell, the first approach requires much less code, but isn't relational. The second approach is relational, but it's requiring far more code (and time), reducing the brevity of my adverts controller in particular, which is actually querying the vehicle's attributes more than the advert's. It's also making "sorting" quite tricky, as user's can sort by "make", "model", "location", "price", etc - attributes which jump between model.

  • Relational data designs are much easier to work with if you have a Relational DBMS and/or a Relational API that you can use. – RBarryYoung Sep 16 '13 at 0:25
  • Can you do both? I.e. use two separate tables, and then use a View to feed data into Laravel framework? – Shamit Verma Sep 16 '13 at 13:18
5

The decision between choosing a relational model over a de-normalized model is typically one of scale and the type of database operations that you anticipate most likely to occur.

A relational database is typically easier to query on and is more efficient for transactional heavy applications while a denormalized schema will be more appropriate if you plan on storing a large warehouse of data that you plan to run analytics or reports on.

If time is your bigger concern and you don't believe this site will have much traffic over the long term then by all means choose schema 1, but I recommend documenting the reasoning behind your eventual decision in the chance that someone else might be maintaining your work in the future and may be struggling with a feature that is at odds with your schema decision.

Myself, I would take the time to make it as relational as possible, but I am a perfectionist.

ProTip: Consider adding VIN number as a natural key to the vehicle table. It will help you identify individual vehicles and it relates to a real easily identifiable attribute of a real vehicle.

  • Thanks for the comprehensive answer. Kind-of leads onto another question I have. All for using the VIN number as a natural key, but in Europe car's are identified more-often by their license registration number - here in Ireland, for example, only registration lookup services are available. VIN lookups are non-existent, which rules this out. – Matthew Ruddy Sep 16 '13 at 1:06
  • 3
    use VIN if you want to identify a vehicle, vehicles can have more than one plate numbers and one plate number can be used for multiples vehicles, although not at the same time. VIN numbers are globally unique. Just have plate number as attribute. – imel96 Sep 16 '13 at 1:51
  • +1 I'd definitely go for the second option. As this answer eludes to, you can probably get by with the first if you don't have performance pressures, but it's not the ideal design. – Matt Sep 16 '13 at 10:08
  • Been thinking about this - the most common thing a user is going to do is "search" the adverts using the front-end options provided to them; make, model, price, location, etc. Using schema 2, I've managed to subquery for the matched vehicle ID's inside of our main adverts query. But if in the future we have 100,000+ adverts, isn't this going to be a rather large bottleneck? Using just one table, the query itself is less complex + we can paginate. With the former, I'm not sure if paginating both the inner & outer query is possible. Our where in clause would contain an array of 100,000 ID's. – Matthew Ruddy Sep 16 '13 at 17:11
  • @MatthewRuddy: you are doing premature optimization here. 100.000 records is not that much for most modern database systems. If you create a view (you read my answer, didn't you?) any search query on schema 2 looks exactly like a search query on schema 1, so why do you insist that your "queries are getting more complex"? – Doc Brown Sep 16 '13 at 20:41
3

It's not a question about 1 or 2 tables. Consider:

Vehicles Table
-------------------
id            int
make          varchar(255),
model         varchar(255),
..
..

or:

Vehicles Table
-------------------
id            int
make          varchar(255),
modelid       int  
..
..


VehicleModel Table
-------------------
modelid       int  
modelname     varchar(255),
..

A good thought out design will make your database that much easier to extend.

  • Right. The OP is not saying anything about future development plans, but if he's ever going to extend the application, he'll be happy that his DB is properly normalized. I've seen people being bitten by this so many times (including myself), that I always do/recommend normalized DBs. – Jan Doggen Sep 16 '13 at 7:08
2

My rule of thumb for such decisions is: if the database is used as the source of that data, and the data is maintained manually in that database (OLTP), keep it as normalized as possible, that will prevent you from horrify headache later on. You should consider to split it into more than 2 tables then.

If, however, the data is not maintained in that db, but only loaded there automatically from a different source and then never changed until the db gets cleared (for example, for reporting purposes, or for an OLAP database), you should use the structure which supports your reporting best, which may be the one-table solution.

Did you consider to use the 2 tables approach together with a joined view? The view could deliver you the data just like schema 1, making your concerns about "far more code" and "tricky sorting" most probably pointless.

0

If these were the only two options, the first choice would be superior. It's almost certain that each advertisement will map to one and only one vehicle; the only time the wouldn't is the edge case where someone sells a car without ever diving it.

Now, if practical, you could gain better utility by having a table for adverts and one for particular models of car: every 2008 Toyota carolla with the LX package has the same design features, for example.

the only times you'd want a one-to-one relationship between tables is if one table is shared with a different program, or if database optimization demands it fur performance reasons. (and in the latter, the split should be hidden from the app if at all possible.)

  • -1 for assuming this is a one-to-one relationship. "a vehicle can be advertised multiple times over its lifespan". – Matt Sep 16 '13 at 10:09
  • the op edited their schema, moving "mileage" from vehicle to advert. There was even a note that multiple adverts would be very uncommon. – DougM Sep 16 '13 at 17:41
0

You should use a RDBMS, no question. Flat-file causes redundant data, and that is never a good thing. Access, MySQL, My-DB or even one on Sun's DB's would be fine. I assume your using an Oracle based DB for your table. Access would be the best and cheapest for desk top, and a Java based db for web-based.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.