270 reputation
17
bio website AndyLeTourneau.com
location
age 25
visits member for 3 years, 3 months
seen Jun 27 at 16:32

Jul
22
awarded  Yearling
Aug
24
accepted Application Settings in Database
Aug
23
answered How to implement a 'default' flag that can only be set on a single row
Aug
23
comment Application Settings in Database
ahh yes I did not consider the fact that it would need to access information_schema.columns as well. You make a very strong argument for Option 2. Thank you much!
Aug
23
asked Application Settings in Database
Aug
18
comment What is blocking and how does it happen?
@Richard, as you had mentioned if a deadlock occurs, then the DBMS would come in and choose a loser.. Is this done automatically by the DBMS? Or would a poorly designed system (one that experiences deadlocks often) halt until a person initiates the DBMS to do so?
Aug
15
accepted Proper Database Partitioning
Aug
5
comment Using IDs from multiple tables in a single column
@gbn Let's say that this database was expanded and you had multiple table entities that tied to one article table. Would your solution be to keep adding ID columns to the article table and expand the check constraint to check across all columns? Or is there a more elegant solution than this for larger scenarios? How about GUID's? I've heard pros and cons to that options.
Aug
3
comment Using IDs from multiple tables in a single column
Not if your dont enforce referential integrity.
Aug
3
comment Using IDs from multiple tables in a single column
How about removing enforced referencial integrity, and then add a constraint to "Division" that wont allow an ID if it already exists in "Department" and vise-versa, this keeps ID's unique across both tables and allows lookup.
Aug
3
awarded  Commentator
Aug
3
comment Using IDs from multiple tables in a single column
@gbn : well I really appreciate your pointing this out, I had never had to dig this deeply into relationships specifics of access before. Would it be better if I didnt force referential integrity? and just had a generic relationship? .. the reason why I have never come across errors with this system is because the code I would use to back the scheme always inserts data as predicted, so never had a problem.
Aug
3
comment Using IDs from multiple tables in a single column
@gbn : I did as you had requested and the errors you are predicting did not happen. But I now understand what your argument is. The reason why it doesnt give me errors is because of the join properties between these tables is set to "1: Only include rows where the joined fields from both tables are equal" . This means that only ID's that are in "Lookup" can be in either "Division" or "Department" (or neither) without breaking the rule. If you would like screenshots or the example access DB file, I can get it to you (if your interested)
Aug
3
comment Using IDs from multiple tables in a single column
@gbn : I dont see how this is worse. If an article is assigned to a "Division" and you wanted to know what "Department" it falls under, you could easily look that up through an easy SQL statement checking for that name. Also it does not limit one article per department/division, the more the Article table grows they would tie to many Lookups (department/division) ID's
Aug
3
awarded  Editor
Aug
3
revised Using IDs from multiple tables in a single column
added 582 characters in body
Aug
3
answered Using IDs from multiple tables in a single column
Jul
27
comment Proper Database Partitioning
thanks @Marian , it sounds like you have quite some experience with 'large' databases. The systems you have been involved with were on dedicated hardware with I would assume a SAN or NAS storage of such, correct? What kind of processing power did these systems have? and how much 'more' could they handle?
Jul
27
comment Proper Database Partitioning
Yes @DTest , I too have never had anything 'large' enough either... but with some of the databases I have designed, this could be a potential problem if I don't start taking this into consideration now.
Jul
27
awarded  Teacher