database querying question SQLite

15 pts.
Hi I'm setting up an SQLIte database within my C application that will be used as an intermediate stage in converting filetypes. My design tries to keep data normalized - for example, i have an Entity table, and in that table each entity has an integer type. There is another EntityTypes table with a string value for the type, and an integer primary key ID. The two are linked using a foreign key constraint. i.e:
"create table Entities(
      ID integer primary key not null unique,
      Name varchar(512),
      Type integer,
      File integer,
      Filepos integer,
      foreign key (File) references Files(ID),
      foreign key (Type) references EntityTypes(ID));"

"create table EntityTypes(
      ID integer primary key not null unique,
      Name varchar(512));"
When I want to insert an entity (foo) into the Entity table, I could do so armed with only the entity type name (bar) by using a subquery (ignoring the file stuff in the table): insert into Entities(Name, Type) values ('foo', (select ID from EntityTypes where Name='bar')); But since I'm diong this all from inside my own app, I can also just make sure that I populate EntityTypes with ID, Name pairs that I know about (enum'd values and string->enum routines for example). My question is: am I defeating the purpose of having a normalized design by keeping these enum values/routines? I presume I am going to see a gain in performance by not having to keep looking up the values in the EntityTypes table with every insert , or am I ignoring a likely caching/other efiiciency? Essentially it feels like I'm adding code that I might not actually need. Your thoughts most appreciated!

Software/Hardware used:
C SQLite

Answer Wiki

Thanks. We'll let you know when a new response is added.

More details about the application could be needed to provide a good answer.

How do you decide that an entity type of ‘bar’ is what corresponds to the ‘foo’ entity ? is it selected by the user, or read from a file, or how ?

Does this app have a GUI ?

Usually, when a list of options is shown to the user, a description or name is shown but an id or code is internally used and stored, so the program can insert or do whatever it needs to do using the id instead of the description (‘bar’). This id-description relation is created when the control is populated, from a database table.

However, if your application reads the entity type from an external source (such as a text file, or a tcp socket) and not from any kind of menu provided by the same application, then there probably isn’t another option than looking for the id in the database.

If that is the case, you might be able to improve performance by storing the contents of the EntityTypes table in memory structures and use some additional routines to get the id and to verify that the type is in fact one of the allowed types (this could also be necessary because AFAIK SQLite lets you define foreign keys but it doesn’t enforce them, so you could end up inserting incorrect entity types into the Entity table even when a foreign key exists). <b>However, these memory structures should be populated from the database table (when the app starts), so, the table is still needed (and recommended).</b>


Discuss This Question: 2  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Kreyszig
    hi, thanks for your reply. in this case the database is actually created at runtime, (and there's no gui that speaks to the database in the context of property types) so the main app is allowed to populate the type table with whatever it likes. The entity type will be read from a known file format, so again the main app will know what it is seeing. It may seem a bit pointless to have a table that only stores a name/value pair, but I can see in the future the entitytype table storing other information. Also, in the future the database may become persistent so I do like the idea of reading from a table if it exists. thanks again for your response, it makes a lot of sense.
    15 pointsBadges:
  • The most-watched IT questions this week: March 30, 2010 - ITKE Community Blog
    [...] database querying question SQLite, asked by Kreyszig and answered by [...]
    0 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: