Code to auto-update tables on set date

Question

Code to auto-update tables on set date

October 27, 2020 at 9:26 pm

This was removed by the editor as SPAM

Viewing 15 posts - 16 through 30 (of 34 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 1

ScottPletcher wrote:

Nothing to do with data modeling, only development. Data modeling must precede coding.
Just don't offer terrible modeling advice and I won't have to point it out.

I believe you missed the j0ke being implied.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 100949 More actions · Answer 2

Jeff Moden wrote:

ScottPletcher wrote:
Nothing to do with data modeling, only development. Data modeling must precede coding.
Just don't offer terrible modeling advice and I won't have to point it out.
I believe you missed the j0ke being implied.

Yep, sorry, I did.

I'm too sleep deprived to be doing this right now anyway.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

thetubageek SSC Veteran Points: 204 More actions · Answer 3

I'm thinking that everywhere you said you wanted to "add columns", you really mean you wanted to "add rows", yes?

Yes, I meant rows. I have no idea why I was thinking columns; maybe it's because the instructor I have for this class has focused on calling it "attributes" instead.

thetubageek SSC Veteran Points: 204 More actions · Answer 4

No and no, to answer your two questions. I saw how the OP was basically ignoring design suggestions and came to the same conclusion as Scott's last statement and your statement above long before they were said but I couldn't let the password column go without at least a fleeting comment.

Then enlighten me: how else do you store a password to access a website within a database, because I have seen it used.

It also seemed that the OP confused rows and columns in the first post and I was curious to see if that was true or they were actually going to make the mistake of adding columns to table every year.

I already stated this; I meant to say rows. I simply had a mental lapse.

Also, you guys say I haven't done any logical data modeling; did anyone not see the ERD that was done?

Also also, this is being done in 12 weeks via Agile with little to no prior information given on how it works and no training given in the processes involved. I came on here asking for help as a result; if you'd rather berate and demean me and not offer what is clearly a higher level of expertise than I have, then why should I even bother being here.

It's people like you who feel the need to "gatekeep" that keeps those who are truly interested in learning more from getting anywhere. And you wonder why data-related fields are in such high demand? Part of it is due to the sheer mathematics of the job market, while another part of it is caused by "gatekeepers" like you holding your knowledge close to the chest like it is some secret ceremony that is required to join your fraternity.

There's a word for that, y'all ... it's called HAZING.

ratbak Ten Centuries Points: 1333 More actions · Answer 5

You got feedback (and a little sass) from some very experienced and talented database experts who have a lot of wisdom to impart. But occasionally the experts vent a little cynicism/shortness. Don't take it personally. Exasperation is often directed as much at instructors who don't adequately impart the basics as at the students.

Scott mentioned some real problems with the design. You probably want to read more about normalization.

Look at Roles in contrast to the better-modeled Enrollment table --

Enrollment associates a Student with a Course & Semester (not sure what Duties is)

But in Roles, you are mixing the definition of a role with the Student & Team that belongs to a role (not sure how Student & Team relate to each other or a role -- may or may not be right for both to be here)

Projects seems questionable. Is a project defined by/associated w/ a single contact and course or multiple?

If multiple, then you need to model it more like Enrollment w/ a Projects table that contains only the definition of the project, and a separate table that associates contacts & courses w/ a project.

You have four different tables -- Users, Contacts, Instructors, Students -- that refer to people that all contain the following columns:

FName, LName, Address1, Address2, City, State, ZIP, Email, Phone1, Phone2 (Not sure what the company reference means in users)

Are contacts, instructors, or students ever/always users? Are instructors ever also students? Should they have the same or different contact info (name, address, phone, email)? If the same, if one updates only one/some of the tables, the data will be out of sync in other tables.

(I'd assert Address1 and Address2 are not necessarily invalid -- that is actually a common nomenclature for referring to the variable sub-city address elements -- Street Address, PO Box, Attention, Suite #, Apt #, etc. -- rather than creating a bunch of very specific but mostly nullable address columns).

And you haven't had real hazing until Joe Celko comes at you 🙂

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 6

thetubageek wrote:

There's a word for that, y'all ... it's called HAZING.

You're the one doing the design. The reason why it seems like hazing is because we're commenting on several aspects of your design that are inadequate but you've shaken off all comments so far. I'm glad we finally got your attention. 😀

Seriously, you need to take a look at all the suggestions made so far (especially the suggestions about normalizing) and re-evaluate your design because folks just don't have the time to actually do a redesign for you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

thetubageek SSC Veteran Points: 204 More actions · Answer 7

OK, I obviously need to provide some additional context:

This database is meant for a website that will show what students have done each semester in their respective courses.

Explanation of tables:

Users: These are individuals from various companies who will log into the website to view the information listed. Unsure if Students, Instructors, and Contacts will have this access, as well.
Instructors: The instructors for the course. Obviously, their contact information is needed so companies can contact them for reference purposes.
Contacts: These are the individuals who are either in charge of the project or requested that the project take place.
Students: The individuals taking the classes.
Semesters: Like a school, they have courses over the Spring, Summer, and Fall every year
Courses: This is the list of all courses provided, regardless of when they are offered.
Teams: Each class is broken up into a number of teams.
Roles: Each team has a number of roles that are assigned to each Team Member (Team Lead, Lead Programmer, Business Analyst, etc.)
Duties: This lists all of the various responsibilities ("accomplishments") that a Team Member is responsible for. The layout for this is meant to be like a bullet list for a resume.
Enrollment: This is a "catch-all" table with the purpose of serving as the report table. Yes, I could create a View and, yes, I have done that, but I wanted the view to work off the contents of a single table rather than coming up with a bunch of crazy links
Projects: This will house the Projects that are established each semester. I had them linked to Courses, but I am going to change that.

Could I consolidate all the "Contact List" tables into one and then have a separate table for User Type or Contact Type? Perhaps, but I prefer that each type of contact/user have its own table for ease of lookups and reporting.

Also, I saw the comments about the Password column and, more specifically, the name of the field. I will come up with something different then Comment that it is the Password in the code.

Again, if there is anything else in my logical structure that I missed or should improve upon, please let me know. I want to take advantage of the minds in this forum to help me better understand what and how I need to do things. Besides, you all were me once and I am sure some of you probably didn't have the benefit of reliable resources online to help you understand things better.

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 8

OK... Let's start off by asking a couple of questions...

Do you agree that a course can have more than one instructor and that a popular course can be taught many times during the same semester, each having it's own instructor? Do you also agree that the entity known as a "Course" needs to stand alone and the same holds true for the entity known as an "Instructor?

Do you also agree that if you do run into the instance(s) when a "Course" is taught by more than one instructor, that your current design would require "duplication" in columns of the "Course" database?

If you agree with all that, then you understand at least my concerns with the current structure of your tables. If it were me, I'd have what many refer to as a "Mapping", "Bridge", "Assignment", or "Join" table between the "Course" table and the "Instructor" table and another between the "Course" table and the "Semester" table. Such tables only contain two columns... the IDs for one table and the IDs for the other table. The PK would include both columns.

I'd also have a separate table each for Email addresses, physical addresses, phone numbers, etc, with similar tables joining them to whatever they need to be joined to. That way, if (for example) a family that has multiple students in the "school" decide to move to a different address, you need only to change it in one and only one place. You might want to consider creating things like the address table as a Type 2 or Type 4 SCD (Slowly Changing Dimension ( https://en.wikipedia.org/wiki/Slowly_changing_dimension ))... just don't fall for the one that uses an EffectiveDate and "IsCurrent" flag because of the hoop you have to jump through to get "Point-in-Time" history when needed.

There's more but most of it is based on properly normalized tables so the above would be a good place to start.

As for the password column... I wouldn't label it as having anything to do with passwords or access and I definitely wouldn't store passwords in any form of clear text nor try to roll-your-own encryption algorithm for it. Use HASHBYTES to generate a one way match and definitely consider a "number of tries" lockout and consider looking up "Salt" ( https://www.google.com/search?client=firefox-b-1-d&q=salt+in+password+hashing ) when it comes to such things as well as enforcing a good password "policy".

You might also want to have a look at some articles on normalization. Here's one of several searches you can start with.

https://www.google.com/search?q=normalisation+tables+in+sql+server+with+examples

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 100949 More actions · Answer 9

thetubageek wrote:

No and no, to answer your two questions. I saw how the OP was basically ignoring design suggestions and came to the same conclusion as Scott's last statement and your statement above long before they were said but I couldn't let the password column go without at least a fleeting comment.
Then enlighten me: how else do you store a password to access a website within a database, because I have seen it used.
It also seemed that the OP confused rows and columns in the first post and I was curious to see if that was true or they were actually going to make the mistake of adding columns to table every year.
I already stated this; I meant to say rows. I simply had a mental lapse.
Also, you guys say I haven't done any logical data modeling; did anyone not see the ERD that was done?
Also also, this is being done in 12 weeks via Agile with little to no prior information given on how it works and no training given in the processes involved. I came on here asking for help as a result; if you'd rather berate and demean me and not offer what is clearly a higher level of expertise than I have, then why should I even bother being here.
It's people like you who feel the need to "gatekeep" that keeps those who are truly interested in learning more from getting anywhere. And you wonder why data-related fields are in such high demand? Part of it is due to the sheer mathematics of the job market, while another part of it is caused by "gatekeepers" like you holding your knowledge close to the chest like it is some secret ceremony that is required to join your fraternity.
There's a word for that, y'all ... it's called HAZING.

An ERD is not a logical model, it is only part of a logical model.

There are no short cuts to a good data design. Everyone wants a quick-and-easy-but-perfect way, which just doesn't exist. If you refuse to read up on logical modeling and to give it a try, that is obviously your choice. But you will end up with a poor design as a result. I must honestly state that. I won't pretend you can skip that step and everything will come out great anyway, because it simply won't, period.

There's nothing secret about normalization. No one's trying to hide it from you. I recommend a good book on the subject. (That's how I learned, the web as a resource not even existing when I started out.) Yes, there are literally hundreds or thousands of web resources for it as well, but some of them are quite awful. At least a book has some vetting and careful editing and consideration (it takes money to print a book, so people take it more seriously). Avoid books by CJ Date or Celko, get something that is less hyper-theoretical and more practical.

You'll start out somewhat slowly doing normalization, but once you get accustomed to the process, it will go much more quickly.

Keep this in mind when dealing with a logical model:

Keep the focus on the data. Developers are far more of a hindrance than a help at this stage. You want to get with knowledgeable business people who can state the data needed and its meaning and relationships.
There are no physical considerations. For example, there is no such thing as an "identity". Rather you might have a "unique integer identifier"; at the logical stage, there's no more details. Also, assume that computers are infinitely fast and large, i.e., don't consider performance at all at this stage.
Creating and storing definitions for entities, attributes (especially) and relationships (often called a "data dictionary" (dd)) is extraordinarily useful, all the way from the modeling process thru the final app(s). This is business data not technical data like column length, etc.. The dd helps you define and clear up qs about data, such as some asked above, much earlier in the process.If you want an example or two of definitions I can provide that. I use tables to store data dictionary info for ease of use. As one example, I use code that copies the stored dd info to/from extended properties in SQL/SSMS. Thus, one can right-click on a column in SSMS and see its detailed *business* definition or even update it ... how nice is that!)

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Sergiy SSC Guru Points: 110206 More actions · Answer 10

thetubageek wrote:

It's people like you who feel the need to "gatekeep" that keeps those who are truly interested in learning more from getting anywhere. And you wonder why data-related fields are in such high demand?

We cannot possibly gatekeep you. Because you're coming nowhere near the gates.

you try jump over the wall, dig under the wall, and, after it all fails, draw a picture of desired city on the wall. While our voices from behind the wall are telling you that it's all stupid and you have to go looking for the actual gates to enter. Well, if you really want to enter. Because for many a picture of the city on the wall is good enough. And it can be done quickly, pretty much anywhere. They call it "agility".

thetubageek wrote:

Also also, this is being done in 12 weeks via Agile with little to no prior information given on how it works and no training given in the processes involved.

Here. You're on board of the "Agile" ship. You picked that pattern and you found it's acceptable to develop data centric systems that way.

I guess you would not mind to take a ride with a driver who's been learning driving the "agile" way. On a car assemblied by workers who had "little to no prior information given on how it works and no training given in the processes involved".

Or having the electrical wiring in your home done "via Agile with little to no prior information given on how it works and no training given in the processes involved".

Or - would you?

But - I know - software development is different. It's ok to be a cowboy there, as long you keep learning new buzzwords and quickly bake something which looks just like requested solution. At least from one angle.

thetubageek wrote:

I came on here asking for help as a result; if you'd rather berate and demean me and not offer what is clearly a higher level of expertise than I have, then why should I even bother being here.

Now, when you've been given quite clear directions in the posts above - are you ready to abandon that ship?

I have some serious doubts about it - the buzz is too loud and it's spooky and lonely to leave the crowd.

So, the gates are open you're gonna be welcomed on the other side. Would you care to enter?

This reply was modified 3 years, 6 months ago by Sergiy.

_____________
Code for TallyGenerator

thetubageek SSC Veteran Points: 204 More actions · Answer 11

OK, it turns out that after meeting with the Development Team, this was a much more complex table design than originally needed. So, I don't need any of this, after all.

Steve Collins SSCrazy Points: 2069 More actions · Answer 12

thetubageek wrote:

OK, it turns out that after meeting with the Development Team, this was a much more complex table design than originally needed. So, I don't need any of this, after all.

May I ask about the alternative approach? Is it more of a document model?

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 13

thetubageek wrote:

OK, it turns out that after meeting with the Development Team, this was a much more complex table design than originally needed. So, I don't need any of this, after all.

Ooooooooo... caveat emptor on that. What people perceive they need or don't need (especially those that complain of complexity) is often seriously inadequate especially for the long large,or fast haul.

And, no... that's not hazing. I'm pointing out a pattern of perception that I've seen repeated more often than it's not and I have a genuine concern for the success of the project you're working on.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

thetubageek SSC Veteran Points: 204 More actions · Answer 14

May I ask about the alternative approach? Is it more of a document model?

This project is part of a 12-week long class. The Development Team for this project advised they are showing Achievements by Team. As a result, they said they only need a few tables:

Users (consisting of User ID, Name, DOB, and Password)
Students (consisting of Student Name, Team (FK), and Role)
Team (consisting of Team Name and Team Project)
Links (consisting of Team (FK), Link Name, and Link URL (links to website with all representative documentation for the projects done)
Achievements (consisting of Team (FK) and Achievement information)
Messages (consisting of Name, Phone, Email, and Message - this is meant for their "Contact Us" page)

They recognized and appreciated the effort put into the previous ERD, but stated that is had to be more simplistic.

Ooooooooo... caveat emptor on that. What people perceive they need or don't need (especially those that complain of complexity) is often seriously inadequate especially for the long large,or fast haul.
And, no... that's not hazing. I'm pointing out a pattern of perception that I've seen repeated more often than it's not and I have a genuine concern for the success of the project you're working on.

I'm not disputing that in the least. My guess is that future classes over future semesters will improve upon the design to the point that it will be at the level of where I envisioned the database being.