CODEWITHSUNDEEP
sqlsql-server
ASD_ASD
ASD_ASD

Reputation: 21

Extending table with another table ... sort of

I have a DB about renting cars.

I created a CarModels table (ModelID as PK).

I want to create a second table with the same primary key as CarModels have. This table only contains the number of times this Model was searched on my website.

So lets say you visit my website, you can check a list that contains common cars rented. "Most popular Cars" table.

It's not about One-to-One relationship, that's for sure. Is there any SQL code to connect two Primary keys together ?

Upvotes: 0

Views: 2207

Answers (3)

CodeSmith
CodeSmith

Reputation: 3197

You are not extending anything (modifying the actual model of the table). You simply need to make INNER JOIN of the table linking with the primary keys being equal.

It could be outer join as it has been suggested but if it's 1:1 like you said ( the second table with have exact same keys - I assume all of them), inner will be enough as both tables would have the same set of same prim keys.

As a bonus, it will also produce fewer rows if you didn't match all keys as a nice reminder if you fail to match all PKs.

That being said, do you have a strong reason why not to keep the said number in the same table? You are basically modeling 1:1 relationship for 1 extra column (and small one too, by data type)

You could extend (now this is extending tables model) with the additional attribute of integer that keeps that number for you.

Later is preferred for simplicity and lower query times.

Upvotes: 0

Russell Fox
Russell Fox

Reputation: 5435

Easiest is to just use a new primary key on the new table with a foreign key to the CarModels table, like [CarModelID] INT NOT NULL. You can put an index and a unique constraint on the FK.

If you reeeealy want them to be the same, you can jump through a bunch of hoops that will make your life Hell, like creating the table from the CarModels table, then setting that field as the primary key, then whenever you add a new CarModel you'll have to create a trigger that will SET IDENTITY_INSERT ON so you can add the new one, and remember to SET IDENTITY_INSERT OFF when you're done.

Personally, I'd create a CarsSearched table that holds ThisUser selected ThisCarModel on ThisDate: then you can start doing some fun data analysis like [are some cars more popular in certain zip codes or certain times of year?], or [this company rents three cars every year in March, so I'll send them a coupon in January].

Upvotes: 0

GuidoG
GuidoG

Reputation: 12014

select m.ModelID, m.Field1, m.Field2,
       t.TimesSearched
from   CarModels m
  left outer join Table2 t on m.ModelID = t.ModelID

but why not simply add the field TimesSearched to table CarModels ?
Then you dont need another table

Upvotes: 2

Related Questions