posted
(If you don't want the context and background of me asking for help, skip to the ***)
I can't take it anymore. I have to ask for help.
I'm a historian. It's what I do, it's what I love. Yes, yes, I'm still just a grad student, but history is my chosen profession. A while back I read an article on how important it is for historians to stay technologically savvy, and I completely agree. Relevancy is the constant struggle of my discipline, and still using typewriters doesn't do much to help the cause (while it's the exception, I do know professors who in fact still use typewriters).
So, long story short, I started looking for opportunities to integrate my historical education with a technological education. This semester I finally had the perfect opportunity fall in my lap. (Warning: Dry historical content to follow. Skip this paragraph if you aren't interested) In 1801, Napoleon sent out a draft copy of the Civil Code to the Courts of Appeal all around France. I'm not entirely sure how many he sent out, but he recieved back 29 sets of "Observations", plus one more from the High Court of Appeals (Cour de Cassation). These Observations range from 2 pages from Ajaccio to around 400 from Rouen and even more from Paris. It's really very exciting stuff, especially considering the legal dynamic of France prior to 1789. There was essentially I line across the middle of France and all of the provinces beneath that line operated under written law while all of the provinces to the north operated under common law (I may have those mixed up. Don't quote me). So by looking at these "Observations" I can trace the continuity of legal structure from the Old Regime to Napoleon, among other things. I'll stop now, but there really is a ton of potential in these documents. And here's the thing - They are completely untapped. This research that I'm doing is not me rehashing someone else and trying to draw a slightly different conclusion. This is groundbreaking, original stuff. I let you in on my secret because honestly, who in their right mind is going to want to plough through around 2000 pages of 200 year old French legal documents.
Back to the whole point of this thread, after gathering all of this material is struck me that this was the perfect opportunity. Instead of sloppily going through the material and writing a paper on it, I would instead create a research database, so that I, and other historians, could easily access the material and then draw conclusions from it. Look at me, being all "for the greater good" and stuff.
But, as I mentioned, I'm a historian. I am not a databaseer. I've spent the last two weeks trying to teach myself Microsoft Access (it doesn't help I only have a Mac at home, so I have to go to the school library to work), and I'll be honest, I'm kind of getting discouraged. I think I'm getting it, but then I try to apply what I've learned and I'm lost. So, I was wondering if anyone might be able to help me out a bit?
***What I have as far as data goes is responses from 30 different courts. These responses can be divided up into Book, Title, Chapter, Section, and Article. (I also have geographic/demographic distinctions, such as Aix is under written law and represents 60% rural farmers, but these are less important than the nuts and bolts of the Code).
What I want out of my database is, preferably, to be able to search by any of these factors and get results. For instance, what courts had issue with Book 2, Title 1, Chapter 4, Section 1, Article 10. Or, just what Courts had issue with Chapter 4 and what specific Section and Article did each court mention. I also would like to add in the other factors, such as written or common law, north or south, primarily rural or urban, but again, those are less important than just making the "Observations" searchable and relatable.
My initial plan was to create a table for each Court (Aix, Angers, Metz, Toulouse, Rouen, etc), then have the fields be Book, Title, Chapter, Section, Article. But when it comes to going beyond just listing the information in the tables, I start having problems. I don't really understand how to use the Primary Key, which I gather is a pretty basic and important concept that I just can't quite wrap my head around. And of course, I'm having a hard time establishing relationships because the primary key confuses me, and I haven't even gotten into Queries or Forms yet. I've been going through the Access Training on the Microsoft website, but like I said, it makes sense at first and then I try to apply it to my project and I get lost. My hope is that by asking actual people for help with my specific project, I can actually get somewhere.
Posts: 2827 | Registered: Jul 2005
| IP: Logged |
posted
It sounds to me like the problem is your underlying table design. Having one table for each court seems like a terrible idea.
I don't use access, so don't remember how different it is. Advice below would be if you were using a common development database like Oracle, MySQL, or DB2.
I'd make a table, called "court". Something like:
code:
create table court( id integer, name varchar(50) )
In this case, the "id" would become the primary key. The additional "id" column isn't necessary, you could make the name the primary key, but keeping the primary key separate from the actual data in the table is good practice. This is called using a "surrogate key". To see the pitfalls of using name (the "natural key"), imagine the likely scenario that the name of a court changes. When doing this, you'd have to update any links to this table using the name.
Then another table would store the article information.
code:
create table mention( id integer, book varchar (50), title varchar (50), chapter varchar (50), section varchar (50), article varchar (50) )
Again we use a surrogate key. If we didn't, you'd probably need to make some combination of the other columns a composite primary key (possible all of the other columns), and that gets rather messy.
Then to link courts with their articles, the relationship is "many-to-many". This means each article could be referenced by many courts, and each court references many articles. We accomplish this with a "tie-table".
Rows in this table simply link the other two tables together in a meaningful way.
To query which courts mentioned which article, you'd write SQL like:
select c.name from court c, mention m, court_mention cm where cm.court_id = c.id and cm.mention_id = m.id and m.book = 'Book Name' and m.article = 'Article Name';
You'd want to add the primary keys, sequences (or identity columns), foreign keys, indexes, and constraints to these tables. Those aren't hard to do, but more than I want to cover here if the preceding advice doesn't work for you.
I'll be able to give more later, but I'm at work, so can't write too long of posts .
Posts: 5656 | Registered: Oct 1999
| IP: Logged |
posted
<typed before Xavier posted - I agree with his advice on a potential design>
Dr - I'm curious. Are you interested in investing a lot of time in relational database design? Will it be something you continue to do after this project is completed?
I'm not sure designing a relational database (and you only have to care about primary keys and relationships if the end result is meant to be a more-or-less-normalized relational database) is the right way to approach what you want to do. It could definitely work. But tagging or annotating documents with keywords could work just as well, depending on how you mean to make this available to others, and might be less work.
If you really want a database, I might have some input on the database design <ETA: See what Xavier posted>. I can say with some confidence that you don't want a table for each Court. Generally speaking, you should have each type of entity represented by one table. A Court table, an Observation table, one or more Civil Code tables, and one or more relationship tables that tie Observations to parts of the code.
But seriously consider doing this another way, if you can. If the Observations refer to the parts of the civil code in a consistent way, you just need something that will index the content and let you search it, and you don't need to build anything custom for that. And even if not, annotation could bring in the consistency you need for good searchability without requiring you to build structured storage.
Posts: 4287 | Registered: Mar 2005
| IP: Logged |
posted
Rough suggestion, chosen for ease of implementation:
First, you'll want a table for the courts. That is, this table will be called something like "Court", and will initially have basically a primary key and the name of the court. Actually, the name of the court could be the primary key, in this case, but you might find it easier to work with surrogate keys for now (don't worry about what that means).
The main reason for having this be a different table is so you can add additional metadata to it later -- like written or common law, north or south, et cetera.
Then, you would have another table for all the parts of the proposed code (book, title, chapter, section, and article). While you could use the combination of those as a primary key, a surrogate key will likely be easier.
Then you will have another table for the relationships between the two. It will contain one column that tells you what court is involved in the relationship, and another column (or columns) that tell you what part of the civil code they were talking about. You could also have columns that indicated things about what their comments were like (favorable, unfavorable, the text of the comment, where to find the comment, whatever).
So, for instance, you might have the following in the Court table (I'm making stuff up, of course):
Aix, rural Angers, urban
And the following in the Code table:
2,1,4,1,10,notes on the article in question 1,3,1,1,2,notes on the article in question
And then the relationship table, which I shall call Observations
Aix,2,14,1,10,notes on what aix said about the article Aix,1,3,1,1,2,notes on what aix said about the article Angers,2,1,4,1,10,notes on what angers said about the article
Then, you would be able to write queries to answer questions like this:
What articles did Aix comment on?
select book, title, chapter, section, article from observations where court_name = 'Aix'
What chapters did Angers not comment on?
select distinct book, title, chapter from code where (book, title, chapter) not in (select book, title, chapter from observations where court_name='Angers')
How frequently did each rural court comment on book 1, title 3?
select court_name, count(*) as number_of_comments from court inner join observations using (court_name) where environment='rural' and book=1 and title=3
Stepping back a bit, let me talk about how databases work. The reason you don't have a table for each court is, each court is not a "type of thing", it is a "thing". Tables are for "types of things", and each row in them represents a "thing".
Primary keys are a column or set of columns that uniquely identify a row in a table. So, a natural primary key for courts in this data set is the name of the court, and a natural primary key for a part of the code is the combination of book, title, chapter, section, and article.
Once you've identified a primary key in a table, other tables (in this case, the Observations table), can have "foreign keys" to it. That just means they have a column or set of columns that uniquely identifies a row in the table they are referring to -- by having the same values as the primary key for that row. That will let us "join" across the rows.
Note: I have made the assumption that all "Observations" are about a specific article. If some of them are, for instance, about a chapter as a whole (or anything that is less specific than an article), then you will need a different (and somewhat more complicated) database structure. Let me know if that is the case, and I will describe the database structure.
Posts: 15770 | Registered: Dec 2001
| IP: Logged |
posted
Yeah, I was getting the feeling that my initial table idea was bad. Thanks for the suggestions so far. Looks like I've got a lot to learn, but the good news is that I do understand what you (Xavier and fugu) are saying. One question (for now ):It sounds like I can use Access, but is Access honestly the best to use? Like I said, I'm having to traipse over to the school anyways, so if there's a better option, I'm open to it. I assume the school will have other options available somewhere.
Scifibum: What I'll say is that I want to become proficient with relational databases and am willing to invest time into that goal. The job market for historians isn't exactly the best, so I'm wanting to learn skills that either will be the X-factor that helps me land a position at a college or university or, failing that, a marketable skill in the world outside of academia. That being said, if this isn't the right project to learn on, I can accept that.
Posts: 2827 | Registered: Jul 2005
| IP: Logged |
posted
If you're on a Mac, Access is probably not your best bet. It's not a bad database, actually, especially for beginners, but there are Mac-compatible ones that'll still do what you want and won't make you track down a PC.
Posts: 37449 | Registered: May 1999
| IP: Logged |
posted
Access is definitely the most user friendly interface for what you want to do, and will probably support all the queries you might be interested in.
This isn't too bad for a very brief overview of relational database design, and reinforces the advice you're getting in this thread. I'd recommend making sure you understand this. Wikipedia is also a good source of articles to reinforce the concepts of relational algebra and normalization. Getting a good foundation in relational theory before you build too many applications should help you minimize your effort in the long run.
Posts: 4287 | Registered: Mar 2005
| IP: Logged |
posted
So, fugu, when you say that a more complicated structure is necessary if, say, Nancy decided to take issue with all of Title VI instead of specific articles, what exactly do you mean?
(2500 posts! woohoo! Only took me four and a half years)
Posts: 2827 | Registered: Jul 2005
| IP: Logged |
Printer-friendly view of this topic
![[Smile]](smile.gif)
.
![[Wink]](wink.gif)
):It sounds like I can use Access, but is Access honestly the best to use? Like I said, I'm having to traipse over to the school anyways, so if there's a better option, I'm open to it. I assume the school will have other options available somewhere. 
