CODEWITHSUNDEEP
c#sql-server
Christopher Vickers
Christopher Vickers

Reputation: 1953

Recommended database structure and retrieval using C#

I have a situation where I have a table of records that looks something like (detail removed for brevity):

CREATE TABLE Records (
    RecordsId int,
    Description varchar(255)
);

I want to add multiple comments for each record using another table such as:

CREATE TABLE Comments (
    CommentsId int,
    Comment varchar(255)
);

I am aware that I can link the tables using a join statement but how would I effectively retrieve the information if there was one Record with multiple comments attached to it, with no set number of comments?

Such as

RecordsId
Description
    - Comment 1
    - Comment 2
    - Comment 3
    - Comment .....

I am aware that I could run a query to get the value of the first table and then run a second query for the second table but this would be extremely slow when I have large numbers of records. I am trying to popullate a C# object with the values.

Upvotes: 0

Views: 137

Answers (1)

granadaCoder
granadaCoder

Reputation: 27884

//add multiple comments for each record //

You need to add a FK from Comment back to Record.

CREATE TABLE dbo.Record (
    RecordKey int,
    Description varchar(255)
);

CREATE TABLE dbo.Comment (
    CommentsKey int,
    RecordKey int NOT NULL,
    Comment varchar(255)
);


ALTER TABLE [dbo].[Comment]
    ADD CONSTRAINT [FK_Comment_To_Record]
    FOREIGN KEY (RecordKey)
    REFERENCES [dbo].[Record] (RecordKey)

..

Other stuff:

Use a schema name to create tables/objects. "dbo" is the built-in default.

I prefer singular noun for the tablename. this is a holy-war. I think the table should be the name of the ENTITY. "Record" and "Comment" are the entitynames. Again, this is a holy war.

I prefer MyObjectKey vs "Id".

Think about this. What is an "EmployeeId"? Is that a db-surrogate-key? Or is that a value on a badge that an employee wears on their shirt? "Id" is ambiguous. "Key" is clearer IMHO.

......

As per your comment, "what would i use with new code?"

I would use Dapper ORM which is a micro orm, but performs really really well.

This article will get you there:

https://medium.com/dapper-net/handling-multiple-resultsets-4b108a8c5172

Pay attention to this comment:

To avoid doing two roundtrips to the database to get customer and orders data separately, the multiple resultset feature can be used:

Upvotes: 1

Related Questions