Looking for a best practice

Question

User1700504264 posted

Hello

I am not sure if I can ask about the best practice in some issue or not, but I hope I found an answer <g class="gr_ gr_119 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="119" data-gr-id="119">for</g> my question

I have three cascaded tables in the database as the following:

File (ID, title)

Section (ID, File, title) where FIle is a foreign key

Topic (ID, Section, title) where Section is a foreign key

Now, I am using the <g class="gr_ gr_644 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="644" data-gr-id="644">bigint</g> datatype for all (ID) columns

but I need to change this to become <g class="gr_ gr_3386 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" id="3386" data-gr-id="3386">as</g> the following:

File ID columns (<g class="gr_ gr_1017 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="1017" data-gr-id="1017">bigint</g>) it will remain as it is now examples (2000, 2001,2002, ....)

Section ID should be formatted as (FileID-SectionID) examples (2000-1, 2000-2, 2000-3, ....), where sections will be sequential in each file and starts from (1) for each file

Topic ID should be formatted as (FileID-SectionID-TopicID) examples (2000-1-1, 2000-1-2, ....) where topics will be sequential in each section and starts from (1) for each section

my question is, should I add a new column for the (formatted ID) in each table?

or there is some other better way or a best practice for doing this?

I am using GridVews, SqlDataSources, ... and the DataKeyName

Thank you

Answer 1

User753101303 posted

Hi,

It would be not my first choice. Basically it seems you are storing in a single column both a pk and an ordering information. My first move would be likely to add a separate ordering column. Is this possible that you'll need to change this order ?

Answer 2

User-271186128 posted

Hi Sir,

but I need to change this to become as the following:

File ID columns (bigint) it will remain as it is now examples (2000, 2001,2002, ....)

Section ID should be formatted as (FileID-SectionID) examples (2000-1, 2000-2, 2000-3, ....), where sections will be sequential in each file and starts from (1) for each file

Topic ID should be formatted as (FileID-SectionID-TopicID) examples (2000-1-1, 2000-1-2, ....) where topics will be sequential in each section and starts from (1) for each section

According to your description, since you want to sort the section and topic, I'm not suggest you you set the format as 2001-1 or 2001-1-1.

In my opinion, I prefer to design the tables as below:

File (ID, title): ID property starts from 2000, might be will set identity column.

Section (ID, FileID, title): The FileID is a foreign key (reference the File table ID property) and ID is an identity column starts from 1.  Then, I could find the Section based on the FileID property, and sort the Section using ID column.

Topic (ID, SectionID, title): The SectionID is a foreign key (reference the Section table ID property) and ID is an identity column starts from 1. Then, I could find the Topic based on the SectionID property, and sort the Topic using ID column.

Best regards,
Dillion