CODEWITHSUNDEEP
database-designdata-structures
sunbomb
sunbomb

Reputation: 1

Best way to define the values for a column in a database

This is a very exploratory question. Suppose we are designing a database for a University; this place has semesters as the basic educational time unit. There are 2 semesters in a year with one half-semester in the summer. They are called terms as a way of describing them. If we want to designate an integer for a term at a point of time (say Autumn 2012 or Spring 2013 or Summer 2013) what do you say is the best composite integer?

For example, would it be 20121 for Spring 2012 where the last digit is the term?

Thanks!

Upvotes: 0

Views: 45

Answers (2)

Brett Rossier
Brett Rossier

Reputation: 3482

Not sure if this will apply %100 to your circumstances, but I work for a college and have run into this already. If your University's school year starts in Fall, then you're in luck because Fall, Spring, and Summer are already naturally in time and alphabetical sequence if sorted ascendingly. If I need a sortable composite, I use a computed field that concatenates the year and semester together.

This is what I'd suggest:

YearSemester:
-------------
YearSemesterID (PK and autonumbering)
Year
Semester
YearSemester (formula Year + Semester, possibly indexed if your DB supports that)

If you really need a number, then you could get rid of the YearSemester field, add a Sequence field, and set up a trigger that would do the Year + Semester concatenation and assign sequence numbers accordingly on each insert/update/delete.

Upvotes: 0

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Well, as I see you tagged this question as database-design I have to argue on assigning an integer to two values: Year and Season. You can't have a table in First Normal Form if you have multivaluated fields.

This will bring you complications when you try to run a query for one of those fields. You won't be able to EASILY answer simple queries like 'How many different years do you have?' without parsing the data. So my advise would be to split the integer you're talking about into fields Year and Season.

Now, if you insist on using an integer to store both values... then your solution seems to be perfect :)

Upvotes: 1

Related Questions