March 25, 2024 at 4:34 pm
Good Morning everyone,
New here, and new to creating database schemas. I am creating a web application where a there are several branches and each branch has users that will enter records in a table that records information about an application. These are : Date the application was lodged, the file reference of the application, the name of the applicant(s), the application type, the lot number, the size, the location, the branch name. Also the file reference has the following format, Branch short followed by the current amount # of the application of the year followed by a '/' then the current year. Also note that every year a new table for the branch is to be created and the current amount of application is to reset to 1(I Think this would be a function in the serverside). What my main issue is that I seem not to know how to structure the way to get a sequence for each branch. Attached is an image of the ERD that I have as of now but not sure if I am on track. If any assistance can be given it would be highly appreciated.
Note: Note sure if this is the correct forum or site to ask this question and/or assistance.
March 25, 2024 at 7:06 pm
Some things are somewhat unclear.
I don't see "district" at all in the ERD, although you reference it extensively in your discussion.
"Entries" is a rather vague Entity. And it looks as though Entries must have a FileRef, and the FileRef must have a Branch_ID. But the Entries may not have a Branch_ID stored with it, thus I'm not 100% sure of the relationship between Entries and Branches directly. It seems like 0 or 1 (?), but that's slightly visual representation of that than I'm used to (and, I must admit, it's been a while since I worked extensively with ERDs).
Did you denormalize Branch_ID to also be stored in Entries (since it would be in the FileRef, storing it again in Entries is a type of duplication). Which controls the Branch_ID more: FileRef or Entries?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 25, 2024 at 8:09 pm
Sorry about that, mistake on my part. Branch and district are synonymous in this specific context. I will make adjustments to the question so it reflects. For the file reference my objective is that I want to be able to capture the # of application entries made for each branch. For example: for this year, up to today, there had been 500 application entries made at branch A. 200 application entries made at branch B. the 500 and 200 will be incrementing by 1 for every application entry made at the respective branch. So the format of the file reference for Branch A would be, A500/2024 and for branch B, B200/2024, etc. My issues is how to keep track of the sequence for number of application entries made per branch. Apologies if I do not fully explain myself in detail, as for the ERD I was looking to capture information as per explanation in this reply. and for your question of denormalize of the Branch_ID, the entries table will need to capture at which branch the application entry was made.
March 26, 2024 at 7:57 pm
> So the format of the file reference for Branch A would be, A500/2024 and for branch B, B200/2024, etc. <<
That sorta violates 1NF, right? Personally I'd suggest not doing that. Just use a single sequential number and have that number identity the A, 500, 2024. If needed, you can then determine the max/next value from that table. I think otherwise you'll have issues with such compound columns, as almost inevitably happens. But if you're OK with what you've got, naturally you can stick with it.
It seems you're needing the relative number per year only to generate the FileRef ID. Can't tell the datatype of the PKs because you had "PK" replace the data type (which it shouldn't, PK should be an additional attribute, but not replace the datatype).
The "child" tables of Branches -- FileReferences and Entries -- if they are fully dependent on Branch (that is, they MUST have a Branch to exist), should be clustered on ( Branch_ID, <own_table_ID> ) (even if that's not formally the PK for the table). That's standard data design: child tables use the parent key + the child key to differentiate rows. It also generally makes for much more efficient processing.
As to "denormalization", maybe I misread the ERD. I took Entries to *require* a FileRef. That may not be right.
P.S. Sorry for the delay, I've been very busy.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply