Root Access

DragonLord

21:03

It's the "multiple" part that makes this tricky, necessitating normalization to 4NF.

marcusdoesstuff

@DragonLord Thanks! I can do some research on that. It's always hard to express this stuff in a google search if you don't know the terminology.

DragonLord

@marcusdoesstuff It is very tricky stuff. You might want to read up on relational database theory and normalization. It's not easy.

Once you understand how functional dependencies work and why normalization is needed, you should be able to design a database that will meet your needs without running into "anomalies" such as duplication of data causing changes in one row not being reflected in another row with some of the same data.

Database normalization

Database normalization is the process of organizing the attributes and tables of a relational database to minimize data redundancy. Normalization involves decomposing a table into less redundant (and smaller) tables but without losing information; defining foreign keys in the old table referencing the primary keys of the new ones. The objective is to isolate data so that additions, deletions, and modifications of an attribute can be made in just one table and then propagated through the rest of the database using the defined foreign keys. Edgar F. Codd, the inventor of the relational model (RM...

If you have any specific questions, you might want to try asking on Database Administrators.

For simpler questions, just ask here and the community and I will try to answer the best I can.

Michael Frank

21:23

@DragonLord Wouldn't this be assuming that every field must have a value? What if they are specified optional?

Or are empty spaces in a database inefficient?

DragonLord

@MichaelFrank There's a special value called NULL that is used to denote that there is no data for that field.

Null (SQL)

Null is a special marker used in Structured Query Language (SQL) to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL Null serves to fulfill the requirement that all true relational database management systems (RDBMS) support a representation of "missing information and inapplicable information". Codd also introduced the use of the lowercase Greek omega (ω) symbol to represent Null in database theory. NULL is also an SQL reserved keyword used to identify the Null special marker. For people new to the subject, a...

In the case of the database you mentioned, a product that has no hazard codes would have no rows in the table listing the hazard codes associated with each product.

It's a lot for me to have to teach, so it's probably best to look it up, read some of the class presentations that instructors have put online, and ask specific questions as needed.

Michael Frank

I have never worked with a DB of any size, just curious as to why they made a big deal of the insertion anomaly in that DB Normalization article when you could simply assign a NULL or predefined value to satisfy the missing field.

DragonLord

Basically, in a 4NF database of the sort you mentioned, you have a table which lists the products themselves, with the hazard codes listed in a separate table. Each row in the hazard code table would contain a product code (listed in the aforementioned product table) and an associated hazard code. Products with multiple hazard codes will have multiple rows, each listing a separate hazard code for that product. Products with no hazard codes will not have a row with that product listed at all.

@MichaelFrank In the case you mentioned, the table is designed with the implicit assumption that each faculty member is associated with exactly one course. That assumption is not true in the real world.

The real issues occur when a faculty member is teaching multiple courses in the same semester. You now have two rows repeating the same information about that person.

What if there's a mistake in one person's name?

Multiple rows will have to be changed because of the redundancy.

Also, what if an instructor isn't teaching a course any more? Deletion of the row specifying the course would cause unrelated information about the faculty member in general to be deleted at the same time.

Michael Frank

21:40

Ahh... I understand. It's not efficient to have n course columns just because certain staff teach multiple courses.

DragonLord

These issues are what we call database anomalies.

@MichaelFrank It also doesn't scale.

An instructor could conceivably teach over a dozen different courses and scores of course sections over the course of his/her career.

How do you represent this data in a relational database? The only way to handle this in an efficient and scalable manner is through separate tables for the course and faculty data.

Bob

22:13

@marcusdoesstuff If you went hierarchical, I'd just say put them in an array (list).

Since you want relational, make a separate table for the data (I forgot the term...)

Table would have two fields, both foreign keys: SDS id and hazard id

Michael Frank

@Bob BORING TABLE METHOD?

Bob

Also, I just noticed the current method adds some extraneous multiline data.

So, a quick'n'dirty fix:

(the whole thing is rather messy, but meh)

@marcusdoesstuff Some discussion here: stackoverflow.com/questions/59482/…, programmers.stackexchange.com/questions/227832/…

End of the day, splitting the list off to a separate table gives you more flexibility, and IMO this data wouldn't really make sense as a set of boolean columns.

I still prefer a hierarchical database though.

@DragonLord This is why I'm recommending staying away from relational DBs.

Relational databases by their very nature are more complex. Harder to design, harder to use.

Unless you have a very large data set or really need every last drop of performance, it's often just not worth it.

Considering there's only ~500 entities total here, a hierarchical model is perfectly fine, and potentially saves a lot of dev time.

You wouldn't even need an index with so little data.

Especially if it's only going to be queried every once in a while.

YAGNI and all that, no point worrying about scaling much if the chance of requiring it is minuscule.

@DragonLord Urk, of course I read this after going over it again :P

(was catching up on messages)

DragonLord

22:30

Yeah, I'm of the opinion that a relational database is not the best tool for this application.

Except that I lack experience in working with any DBMS other than a relational system.

marcusdoesstuff

oop i have some catching up to do here

Bob

@DragonLord Well, you wouldn't be using a DBMS in this situation.

(the only other ones I can think of are key-value anyway)

hierarchical are typically stored in a flat file, or use the filesystem directly

Popular flat file formats are XML and JSON

XML lets you define a schema, while JSON is a bit simpler and easier to work with

my script above outputs JSON

would be trivial to adapt it to XML, or to shove its results into any other DB format

@DragonLord If you've done anything with XML or JSON, or understand the Windows Registry structure, you basically know how the common hierarchical structures work.

DragonLord

I do understand the use of XML as a hierarchical database format but I have never written an application using an XML database.

Bob

@DragonLord If you've ever serialised to XML, that counts too.

DragonLord

Never did that, either.

Bob

22:41

You don't necessarily have to explicitly do it. JSON and XML map very easily to objects in OOP languages.

Relational DBs tend to be more complex, and the DALs are similarly complex.

marcusdoesstuff

@bob I was definitely assuming that I'd want a separate table for the hazards if I went with a relational database... But now that I realise that I need less data than I previously thought, a hierarchal database certainly does seem to make sense too. They are still easily scalable right?

Bob

@marcusdoesstuff To an extent.

You can easily deal with hundreds, thousands of fields and entities.

Once you approach the millions range, you run into issues.

It also depends on how often the queries happen.

If it were a multi-user website or server, you'd want something fast.

A DBMS (whether relational or key-value) makes indexed columns easy.

marcusdoesstuff

right well it will never reach those numbers.

Bob

A hierarchical DB, since there aren't any really common DBMSes, tends to require either a manually-implemented index or iterating through all values (relatively slow).

If you're just querying now and then, it doesn't really matter if the query takes a second, or even 5-10s in the worst case.

But 500 entities with, say, 100 fields each? I'd be surprised if that took 100ms on a modern system.

The only potential problem for you is you can't really work with this in Access. Access relies on a relational model.

You'd basically need to DIY your queries.

marcusdoesstuff

my only reasoning for using a relational database was that it sounded easy to add extra tables on the side and add extra functionality... and that as i understood how it worked, it seemed like a pain to learn other methods, hah.

Bob

22:45

Now, JSON and XML are easy enough that that's pretty trivial in any language (Python or JS preferred), but it still requires scripting rather than a fancy graphical designer.

marcusdoesstuff

i'm not lazy though... just restricted on time!

Bob

@marcusdoesstuff Consider YAGNI, though.

How likely are you to need to add more?

marcusdoesstuff

xkcd.com/1319

Bob

If you understand relational very well, then you can still use it.

But if you 'kinda' understand it, it might not be worth the effort.

End of the day, it's up to you.

There's arguments for both sides (consider scale from the beginning because starting small can lead to a messy codebase/design, or start small to save on initial dev costs because it'll likely never need to scale)

marcusdoesstuff

access can do hierarchal, right?

Bob

22:47

Nup.

marcusdoesstuff

well that may just swing it to relational anyway

Bob

Well, you could emulate it but it's messy.

marcusdoesstuff

i know! i know! access isn't great. :(

Bob

IMO your data is primarily hierarchical anyway and forcing it into a relational structure will be messy regardless.

But it's not that deeply nested either, so it's not too bad.

You'd just end up with either ~6 extra tables (one for each multiline section in the above script) or one table with an extra field denoting the 'type'

I'm not a fan of that kind of design, personally. But others like it, and it might work for you.

Anyway, you can use the above script to do basic parsing (modified as necessary) and then pump data into whatever database format you want.

marcusdoesstuff

i'd not mind picking up another piece of software but my co-workers are not the types to pick up new stuff so it'd be easier for them to use what they know.

yeah, that's the hardest part, for sure!

Bob

22:51

Seeing as you don't really know any scripting languages yet, Python is probably as good as any other to pick up.

marcusdoesstuff

yeah i might look into it

Bob

And a heck of a lot easier than fiddling with Excel formulae and VBA, IMO

Ramhound

Python? to start learning programming?

marcusdoesstuff

yeah

Ramhound

eeeek

marcusdoesstuff

22:51

i can use it in blender too

Bob

@Ramhound It's more of a "I need to do this task, and need to learn some language"

It's a scraper anyway. Not many other suitable languages.

And Python is often considered a pretty good starter language.

(note: the above script is in Python 2, though 3 is fundamentally similar...)

marcusdoesstuff

i'll do what i always do... scour the internet for something that looks similar and poke it till it works!

Ramhound

alright, a scraper, thats a good choice.

Bob

@marcusdoesstuff Heh. You can use the above script as a starting point.

marcusdoesstuff

great!

Bob

22:53

@Ramhound Other options off the top of my head are Perl (that's an 'eeeek'), PowerShell (...*meh*, probably a little more obscure) and Bash (ugh).

JS in node.js might've been an option, but he might want to import it into an Access DB, and I'm not sure if node has the correct drivers to talk to it.

marcusdoesstuff

it certainly doesn't look much more scary than css/html.

Bob

And the async model is probably more confusing too.

marcusdoesstuff

(if it is then don't tell me)

Bob

@marcusdoesstuff Just a note, the above script is rather messy

It could certainly be cleaner, but I didn't really bother for a PoC

marcusdoesstuff

heh okay :)

Bob

22:54

And scrapers/parsers tend to be messy anyway.

Especially when dealing with somewhat-ill-formed data.

marcusdoesstuff

yeah makes sense

Bob

(at least the data is self-consistent)

Ramhound

I have this program that lets me use my 360 controller connected to programs like World of Warcraft, well it has decided to do the old, i will run the silently crash on you thing

marcusdoesstuff

it looks tidy when you have been looking at these SDS all day

Ramhound

Why can't people program anymore..

marcusdoesstuff

22:55

more people do, don't they?

at least as a profession...

Bob

@Ramhound Seems easy when you're in your own little world, but once you deal with hardware... shudder

Ramhound

It is such a useful program works great, best choice for this task, but it really is horrible from a quality perspective.

Bob

<== stays firmly on the software side

Ramhound

Who says I don't?

lol

marcusdoesstuff

but i'm a designer really but i stick my foot in other stuff all the time.

Bob

22:56

@Ramhound I mean the general 'you', not you specifically.

Ramhound

Ahhh

Bob

I've come close to tearing my hair out just from trying to get a low-level keyboard hook working.

And that's not even diving into kernel mode bs

marcusdoesstuff

Thanks again @Bob, @DragonLord and everyone else. I should get ready for bed. See you tomorrow!

Bob

@marcusdoesstuff bye? (it's morning here :P)

marcusdoesstuff

Haha, did you even sleep?

Bob

22:59

Uh... a bit?

marcusdoesstuff

At your desk?

Bob

*wipes drool off keyboard*

no, of course not!

marcusdoesstuff

Been there...

See you in the afternoon then :P

Bob

heh

'night

Transcript for