0

Hello was wondering what the better approach is: the target database will only have about 100,000 rows

To have one table like below:

AllCars

ID | Car_Name |    Car_Model    | Car_Type  | Car_Features | Car_Location
______________________________________________________________________
1  |  Nissan  | Ultima Explorer |   4D      | Airbag Power | TX

SQL: Select 'X' from AllCards where Car_Model LIKE '%ultima%';

or to break it up into many tables

Car_Types

ID | Car_Model |
----------------
1  | Nissa Ultima |

and write joins:

SQL: 

   SELECT'X' from Car_Types c 
     JOIN AllCars a 
     ON a.ID = c.ID  
     WHERE Car_Model = 'Ultima';
Improve this question
5
  • 2
    You should follow normalization principles when deciding whether to have one table or multiple. Commented Dec 27, 2014 at 3:03
  • The condition LIKE '%ultima%' forces MySQL to check all records in the table AllCars. But if you'll normalize database, possible Car_Types will have far fewer records than 100,000. Commented Dec 27, 2014 at 3:09
  • you're right i was just wondering in terms of efficient how much better the join was than the like. Commented Dec 27, 2014 at 3:10
  • 1
    Year, make, model, style - JOINs make this better. Normalize and measure; denormalize only when performance dictates. 100K rows isn't a lot for a properly indexed schema. Commented Dec 27, 2014 at 3:10
  • I agree with duffymo. In this case, only when you get to multiple options for a vehicle, make sense, but things of only one available per car (4-cyl, 6-cyl, 8-cyl, color, doors, fabric), but other thing that could be part of a "package" where individual components may be 2-3 vs 10 (such as electronic features) would go into a secondary features table per specific car. Commented Dec 27, 2014 at 3:15

2 Answers 2

Reset to default
2

I'm confused. The two queries are not the same. In one case:

where Car_Model LIKE '%ultima%'

And in the other:

WHERE Car_Model = 'Ultima'

If you think that 'Ultima' is a valid value for car_model, I would suggest that you store 'Ultima' in the field, with nothing else. Perhaps you should have a multi-part identifier for the model.

In any case, it doesn't make a big difference whether you store the raw data in one table or whether you use a reference table -- so long as you store the value consistently. The "consistently" often leads to using a reference table approach, which would be the second approach. The key, though, is separating out the different components so you can use equality for the comparison rather than wildcards.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Hi Gordon Linoff. sorry to disturb you from here. I am in critical situation to handle on sql module. I have updated my question here. please provide me a suggestion on this stackoverflow.com/questions/27664546/…
0

It is almost always better to normalize your data. However, you must ensure that you are not performing joins for even the most frequent queries.

There is no right or wrong answer to your question. It all depends on your use case, what kind of data you are going to store, how frequent it will be queried and how it would be queried, meaning what are the fields queried for most frequently. So it would be better if you would provide more details about the nature and frequency of your queries.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.