Skip to main content
Software Engineering

You are not logged in. Your edit will be placed in a queue until it is peer reviewed.

We welcome edits that make the post easier to understand and more valuable for readers. Because community members review edits, please try to make the post substantially better than how you found it, for example, by fixing grammar or adding additional resources and hyperlinks.

5
  • 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. Commented Sep 16, 2013 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. Commented Sep 16, 2013 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. Commented Sep 16, 2013 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. Commented Sep 16, 2013 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"? Commented Sep 16, 2013 at 20:41