Database table setup

[Rowboat]

What is the best method for storing this type of information in a database?

Would it be one table or two, if its two tables do you link them somewhere other than in queries?

Currently I have

make table
ID make
10 Ford
11 Honda

model table
ID model
100 F150
101 Focus
102 Accord
103 Civic

Do i create another table that relates them by ID?
Make Model
10 100
10 101
11 102
11 103

or do I join them into one table
ID Make Model
10 Ford F150
....

Or is there some other way that is much better?

Thanks for reading, I'm just trying to get my head around how to convert some of my spreadsheets into a database format and some things just don't come naturally to me.

 

[QED]

I would put them in one table, but it depends...

If you have a "make" table with just two fields (ID and make), what extra information does "ID" give you that "make" does not? The "make" field should be unique among your entries, so you don't need the ID field-- leaving you with a make table with a single field-- which is, in most cases, a waste. Same thing for the "model table".

Just put them both into a single table, unless you need a table of just makes or just models for some other purpose...

 

[reverend boltron]

I would go with the make_model table personally. I think it holds data integrity a bit better, but I'm not a SQL expert.

 

[Rowboat]

Originally posted by: QED
I would put them in one table, but it depends...

If you have a "make" table with just two fields (ID and make), what extra information does "ID" give you that "make" does not? The "make" field should be unique among your entries, so you don't need the ID field-- leaving you with a make table with a single field-- which is, in most cases, a waste. Same thing for the "model table".

Just put them both into a single table, unless you need a table of just makes or just models for some other purpose...

Thanks for the input, i'll do it that way and see if there are any bumps in the road later on.

 

[troytime]

depends on the application really

but i would have a table for makes
and a table for models
and the table for models would have a column for make, and it would use the make_id from the make table

 

[EagleKeeper]

Originally posted by: troytime
depends on the application really

but i would have a table for makes
and a table for models
and the table for models would have a column for make, and it would use the make_id from the make table

:thumbsup:


If you every wanted to pull up all of one model, numeric indexing is more efficient that string matching.

 

[Crusty]

Originally posted by: Common Courtesy

Originally posted by: troytime
depends on the application really

but i would have a table for makes
and a table for models
and the table for models would have a column for make, and it would use the make_id from the make table

:thumbsup:


If you every wanted to pull up all of one model, numeric indexing is more efficient that string matching.

The only time you need a 3rd table is when you have a 'has many and belongs to' relationship. If it's strictly multiple childs per parent there's no reason to have a 3rd table, just reference the parent_id in the child table like the above posters have said

 

[drebo]

Personally, I would do it this way:

Table 'make':
mfg_id (pk)
mfg_name

Table 'model':
model_id (pk)
mfg_id (fk)
model_name

No need for three tables, as this is only a one-to-many relationship. Three tables is only needed for many-to-many. As a model will only ever have one maker (at least in the context of this example (cars)), you will never have a many-to-many.

 

[presidentender]

Originally posted by: drebo
Personally, I would do it this way:

Table 'make':
mfg_id (pk)
mfg_name

Table 'model':
model_id (pk)
mfg_id (fk)
model_name

No need for three tables, as this is only a one-to-many relationship. Three tables is only needed for many-to-many. As a model will only ever have one maker (at least in the context of this example (cars)), you will never have a many-to-many.

:thumbsup::thumbsup: