Advice for a "how-to" situation.

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Advice for a "how-to" situation.

Michael Tiernan
This might be a general RDBMS question but since I'm using sqlite
specifically, I hope it passes basic relevancy tests.

I have a table defined as:

CREATE TABLE "CPUModelDictionary" (
        `vendor_id` TEXT,
        `cpu_family` INTEGER,
        `cpu_model` INTEGER,
        `cpuid_level` INTEGER,
        `cpu_model_name` TEXT,
        `cpu_cores` INTEGER,
        `cpu_MHz` INTEGER
);
CREATE INDEX `idx_CPUModels` ON `CPUModelDictionary` (
        `cpu_family`,
        `cpu_model`,
        `cpuid_level`,
        `cpu_cores`,
        `cpu_MHz`
);

that contains rows like this:

'GenuineIntel',6,62,13,'Intel(R) Xeon(R) CPU E5-2650 v2 @ 2.60GHz',8,2620
'GenuineIntel',6,63,15,'Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz',8,2600
'GenuineIntel',6,79,20,'Intel(R) Xeon(R) CPU E5-2620 v4 @ 2.10GHz',8,1200

The five fields used in the index create a unique identifier.

I want to reference these rows via a single reference field but I'm not
sure if there's a "smarter" way to do it. I considered just creating a
field that's a concatenation of the five fields and using that as the
unique link to the rows but I'm sure that it's not the best way to do it.

(The objective is to have a single field in another table that
identifies the CPU used in a system. It will be a many-to-one reference
to this CPUModel.)

Any advice? Pointers to documentation offering advice will help too.

Thanks for everyone's time.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Advice for a "how-to" situation.

Peter da Silva
Seems like you'd want to create a rowid primary key (internal_id integer primary key) that's the foreign key for the other table.

´╗┐On 2/15/18, 1:19 PM, "sqlite-users on behalf of Michael Tiernan" <[hidden email] on behalf of [hidden email]> wrote:
    (The objective is to have a single field in another table that
    identifies the CPU used in a system. It will be a many-to-one reference
    to this CPUModel.)
   
    Any advice? Pointers to documentation offering advice will help too.
   
    Thanks for everyone's time.
    _______________________________________________
    sqlite-users mailing list
    [hidden email]
    http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
   

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Advice for a "how-to" situation.

Simon Slavin-3
In reply to this post by Michael Tiernan
On 15 Feb 2018, at 7:19pm, Michael Tiernan <[hidden email]> wrote:

> This might be a general RDBMS question but since I'm using sqlite specifically, I hope it passes basic relevancy tests.

I don't think we have any problem answering SQL questions here, as long as they're good ones.

> I have a table defined as:
>
> CREATE TABLE "CPUModelDictionary" (
> `vendor_id` TEXT,

Make that TEXT COLLATE NOCASE.  It will save time and programming when you want to do searches and sorts.

> `cpu_model_name` TEXT,

and that one too.

> The five fields used in the index create a unique identifier.

Then do CREATE UNIQUE INDEX.  That way SQL will enforce the uniqueness.

> I want to reference these rows via a single reference field but I'm not sure if there's a "smarter" way to do it. I considered just creating a field that's a concatenation of the five fields and using that as the unique link to the rows but I'm sure that it's not the best way to do it.

I agree with Peter Da Silva.  Declare the unique rowid column for the "CPUModelDictionary" table:

CREATE TABLE "CPUModelDictionary" (
        `rowid` INTEGER PRIMARY KEY,
        `vendor_id` TEXT COLLATE NOCASE,
        `cpu_family` INTEGER, etc.

From that point onwards you have a unique number which refers to each row in the table.  When you want to refer to a specific combination of characteristics you can use that single integer.  This is called "normalisation" and is a standard way to do things in SQL.

By the way, your use of backticks to identify column names is not wrong, but it is rarely done these days and can lead to problems because strings in SQLite are delimited with apostrophes 'string' which look almost the same.  Most SQLite programmers use column names without any surrounding characters.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Advice for a "how-to" situation.

Michael Tiernan
On 2/15/18 2:55 PM, Simon Slavin wrote:
> By the way, your use of backticks to identify column names
That's a quirk of the "schema" command that I used to export the sample
that I'm playing with.

However, thanks for the reminder.

And thanks to everyone for the advice on how to do this.

I'm going to dive in now.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users