I am an LlblGen Pro user, although currently at version 5.8.

I apologize if this is a Database Design 101 type question, but my background is in Key/Value databases, and fairly recently got to Relational. And now, with the options provided by LlblGen, I'm not sure what the best practice for my situation is. I also apologize for my ignorance if my relational skills / terms are incorrect.

I want to present my user with a table of, for example, first and last names. When they pick one of these records, they get to edit all the data for that person (address, gender, hair color, etc.) I refer to these as the Header info and the Body info. I only need the Body info for a single record, so I don't want my client or my server to have to read anything unnecessary. I don't want to read everyone's full data just to show the first and last names.

What's the best way, performance wise, to accomplish this?

1. Have two tables (e.g. Names and AllTheRest), one with just first and last names, read all those records as an entity collection, display that in the grid, then read the rest of the data as a single entity from the second table based on the selection is made?

2. Have one table, with a stored SQL procedure that returns just the first and last name? Wouldn't this require the server to read every record to retrieve just those columns, something that I'm trying to avoid? If I index those columns? What if I need to show 12 columns instead of two - 12 indexes?

3. One table, use a LlblGen QuerySpec (or Linq?) to retrieve all the first and last names? Again, does this read all records?

4. Something else? (Projections? EntityView? Something else I'm missing?

Thank you in advance.

Ray

timbered wrote:

I am an LlblGen Pro user, although currently at version 5.8.

I apologize if this is a Database Design 101 type question, but my background is in Key/Value databases, and fairly recently got to Relational. And now, with the options provided by LlblGen, I'm not sure what the best practice for my situation is. I also apologize for my ignorance if my relational skills / terms are incorrect.

I want to present my user with a table of, for example, first and last names. When they pick one of these records, they get to edit all the data for that person (address, gender, hair color, etc.) I refer to these as the Header info and the Body info. I only need the Body info for a single record, so I don't want my client or my server to have to read anything unnecessary. I don't want to read everyone's full data just to show the first and last names.

What's the best way, performance wise, to accomplish this?

Performance and relational model design are two things which can influence each other, but at the basis, the relational model first has to make sense. So design your entities in the way one would do normally: all fields which have a 1:1 relationship with an identifying field are placed in a single entity and after that you define relationships between these entities. For 1:1 relationships between entities, these are often chosen if the FK side is optional or is an identifiable unit on its own.

It often works top down tho, hence quickmodel works that way too. I.e. in your domain you often recognize entities without assigning fields to them first, like 'customer', 'order', 'product' etc. and once these are defined you can define relationships between them and assign the fields they contain.

You have defined an entity with first and last name, but is that an identifiable unit on its own? It's just a name. What if two people have the same name, does that mean you have just 1 instance? The same thing goes for e.g. address. People often model that as a separate entity, but ... is it? It's not shared with other entity instances most of the time.

A handy trick could be this: how are these values edited? If you pick them from a list, you could say 'they're a separate entity', as picking from a list could mean you define a 1:n/m:1 relationship. However if you fill in the values, they're not.

Names don't identify a person, so I wouldn't show them in a list to select, because 2 people with the same name are two different entity instances, but in your model they're the same person...

1. Have two tables (e.g. Names and AllTheRest), one with just first and last names, read all those records as an entity collection, display that in the grid, then read the rest of the data as a single entity from the second table based on the selection is made?

2. Have one table, with a stored SQL procedure that returns just the first and last name? Wouldn't this require the server to read every record to retrieve just those columns, something that I'm trying to avoid? If I index those columns? What if I need to show 12 columns instead of two - 12 indexes?

3. One table, use a LlblGen QuerySpec (or Linq?) to retrieve all the first and last names? Again, does this read all records?

4. Something else? (Projections? EntityView? Something else I'm missing?

Thank you in advance.

Ray

Thank you Frans.

But I guess I simplified my example too much. My "Person" entity actually has over 100 data items, and my initial display allowing a user to select one to edit (a DevExpress grid with the Find panel enabled) has 7 columns. Showing just first and last name was just an example to ask about the physical data table design.

So, I guess my question boils down to: Should I have one entity (and thus physical table in my DB) with 100 fields, or two entities, one with 7 fields the other with 93, and a 1:1 relationship? Whenever my user wants to start looking / editing my Person, I need to read those 7 data items for every record (to show my selection grid), but only one record for the remaining 93.

What's the best model to achieve that with the minimum amount of physical table reading?

Maybe I'm showing my mainframe background, and worrying about disk reads too much. I suppose it probably doesn't make that much difference unless I'm scaling for 100K+ records, which I'm not, and I should just keep my data model straightforward and let the DB engine work out the details?

Ray

timbered wrote:

Thank you Frans.

But I guess I simplified my example too much. My "Person" entity actually has over 100 data items, and my initial display allowing a user to select one to edit (a DevExpress grid with the Find panel enabled) has 7 columns. Showing just first and last name was just an example to ask about the physical data table design.

So, I guess my question boils down to: Should I have one entity (and thus physical table in my DB) with 100 fields, or two entities, one with 7 fields the other with 93, and a 1:1 relationship? Whenever my user wants to start looking / editing my Person, I need to read those 7 data items for every record (to show my selection grid), but only one record for the remaining 93.

If you're concerned about the disk IO, then you could use the 2 table approach. You could also do a projection fetch on the 1 big entity if you merge the two, and fetch the PK + firstname + lastname, and after that just fetch the single entity for the PK selected with a simple entity fetch. Your API offers field exclusion/inclusion for entity fetches, so you can e.g. specify to only fetch a given set of fields if there are a lot of fields of those 100 which have a lot of data. 100 fields suggest it's a projection from a multi-table model. Look for the fields which have duplicates in a lot of rows: this suggest there's a 1:n relationship between the fields within the table, so you could opt to normalize these fields into their own entity.

However if this table is merely used in a readonly fashion, you could also simply use what you have.

What's the best model to achieve that with the minimum amount of physical table reading?

Maybe I'm showing my mainframe background, and worrying about disk reads too much. I suppose it probably doesn't make that much difference unless I'm scaling for 100K+ records, which I'm not, and I should just keep my data model straightforward and let the DB engine work out the details?

Yeah I wouldn't worry about that. database systems have great caching systems nowadays, and especially for data that's not updated, it's read once and re-used from memory after that. Unless you give your system a super small set of ram, I think you'll be fine. (this forum runs on a 2 core 4GB VM with 120,000+ messages in the DB, you'll be ok )

Thanks. You have convinced me that I'm getting too hung up on worrying about performance on a system that isn't big enough for that worry.

If I was storing 1M+ records, I would let that drive the model design, or at least take it into consideration. But for my current task, I think it'll be better to keep the model straightforward, and let the DB engine and Llblgen work out the details.

So, single table with a projection sounds like the winner.

Thanks again, Frans!

Ray