3D Database Concept (1 Viewer)

[systemx]

Hi there,

I'm still very new to Access....and learning it's strengths and weaknesses. A thought occurred to me today. Currently you have to build tables and set up relationships between them in order for 'searching' or 'querying' to work effectively.

Surely...it would make sense to develop a 3D database application..ie x,y,z axis in a table. That could eliminate the need for relationships between tables, as all of your data could potentially sit in one table (or at least reduce the number of tables required in a large database).

Does anyone know if this has been considered (I'm sure I'm not the first) or if there have been serious attempts at developing the concept into an application?

Cheers

Rob

 

[Len Boorman]

Hmmm Think these are called three dimensional arrays.

With an array of this type you
1) Would probably be left with many empty locations
2) These empty locations would of course absorb memory
3) There would be a great deal of data duplication
4) Referential integrity would be very difficult indeed
5) Updates would be a nightmare

and I am sure that there are many other observations that could be made.

Access is not a Database. It is Relational Database Management System.

It has the tools to enable you to build a Relational Database. Now there is lots of Relational Algebra and Relational Calculus that is pretty heavy going but save that for asleepless night.

Admire the thought on 3D but think that as you learn more you will find that what you may believe at the moment are weakness's are quite the reverse.


Stay with it

Len

 

[giovi2002]

Hi Systemx, there are some vector databases out in the world.
Lately I heard on the radio a dutch company designed a vector database (multiple coordinates to store data) to enhance text search. Instead of ansi code the text is being stored as vector data which massively enhances searching through text

 

[Len Boorman]

Are you talking data warehousing/data mining ?.

L

 

[Len Boorman]

Ignore what I have said. had a quick look on the internet and think you will find this is quite a different animal and really not comparable to Relational Database's
Len

 

[giovi2002]

Are you talking data warehousing/data mining ?.

L

Well, it's just another way to store data (think about the cubes in datawarehouses). Vectors can be far beyond 3d (think about 10D etc).
The logic between data is well captured in a relational database, but when searching 'text fields' you have a problem because of the physical storage of a text field. A text field itself is an attribute and therefore unstructurered data.
A vector database stores text fields in a more proficient way, less storage needed and because of the vector design much quicker to search through text

 

[Pat Hartman]

Currently you have to build tables and set up relationships between them in order for 'searching' or 'querying' to work effectively.

-No. The purpose of a relationship is to provide the information necessary to enforce referential integrity. If you are not enforcing RI, there is no need to go to the bother of defining relationships although Access does use the defined relationship to "help" you when you create queries. Access will automatically draw join lines if relationships are defined.

As to your 3D database design. It already exists. It is called Pick and is considered old technology.

 

[systemx]

Thank you all for your feedback. I appreciate the insight...and yes, realise I still know very little about the whole area!

I'm interested to read and learn more...so will check out some of the mentioned products.

Thanks

Rob

 

[Susan Owen]

Hi Rob,

One of my colleagues showed me his database design that is completely normalized and generic. You can use this for anything, whether a business manufacturing organization or products, or services, or non-profit. It's a sort of a "one-size fits all".

He told me that it was copyrighted. I wonder just exactly what that means, like in music, you can copyright the words, but it's not possible to copyright melodies and chords. So I can understand that an application can be copyrighted, but I don't think it's possible to copyright a database design. Who would ever know if someone else used your design? Besides, I rather think that he got this from somebody else to start with. Any information / ideas from anybody? I think it would be great to have this design available to use in production, and I've been searching the Internet but can't find it anywhere. Does anybody know of anything similar?

I'm sorry for double posting this, as I also posted it on the database design tutorial thread, but thought this was better than starting a new thread.

 

[Pat Hartman]

Did he let you sit down and analyze it or did he just wave it in front of your face?

SharePoint is designed to be able to store any type of data. That doesn't mean that I would ever use SharePoint to manage my database. Its ultra normalization results in the inability to enforce RI plus performance issues that limit its usefulness to tables of less than 10,000 rows.

 

[Susan Owen]

Did he let you sit down and analyze it or did he just wave it in front of your face?

SharePoint is designed to be able to store any type of data. That doesn't mean that I would ever use SharePoint to manage my database. Its ultra normalization results in the inability to enforce RI plus performance issues that limit its usefulness to tables of less than 10,000 rows.

Hi Pat,

Yes, he did let me analyze it. Thanks for the info on SharePoint, I'll check it out, since it might be good for really small databases, those are the ones I usually work with.

 

[The_Doc_Man]

Susan, in regard to the sub-thread on what it means to copyright a database.

The problem with copyrighting anything is that not everything is copyrightable. I'm not a lawyer but I work with the U.S. Government and have to consider U.S. Copyright law. The same principles apply to copyright law, patents, and trademarks.

Basically, here's how it works.

You cannot register, copyright, or patent something that is a basic natural element. For instance, I cannot copyright the word "the" and expect to get any royalties. I cannot copyright each individual word in the dictionary. The only thing I can copyright along those lines is a specific combination of words long enough to qualify under the U.S. Copyright Law of 1975. And any combination of words that is very short is unlikely to qualify based on commonality considerations. You need something that qualifies as an essay, short story, novella, novel, or epic. I've got five copyrighted novels (not published yet...).

In math, I cannot patent a mathematical formula that could be derived from first principles. I cannot patent numbers. OR copyright them. I cannot patent or copyright natural formulas derivable from simple observations. So forget about patenting gravity. If I perform some engineering calculations and design something physical that isn't derivable from first principles, THAT can be patented. Like a specific design of an electric streetcar or trolley.

I cannot trademark anything that is so commonplace as to be in public domain. For instance, I cannot trademark basic geometric designs that are likely to be part of a more complex pattern. I can't trademark a circle. I can trademark a combination of circles and arcs that looks like mouse ears, but another company has that one already. Disney.

Therefore, if your friend has a copyrighted database, I believe it. But if I also create a perfectly normalized database on a different topic, he would find that he had no claim against me even if my database could have been created using his database.

Why? Because normalized databases can be derived from a starting subject and some basic methods. The fact that they are derivable from basic methods means that one person's claim of generality doesn't block me from creating anything else equally as general. In fact, some years ago, I built a general hierarchical database. We could customize it for anything from a waste treatment plant to an interstate pipeline to a department store chain energy management system. I know it would work for those things because that was our customer base. I don't own the patent on the software because it was "for hire." But it was patentable. The fact that I built it from basic components wasn't a problem. It was the combination that made it unique and therefore protectable as intellectual property.

Therefore, your friend's database is nice and I wish him well. But no matter how general it is, anyone else can build one and be free of claims. They just can't copy your friend's base code to do it.

 

[AlanS]

A few points to consider here (based on U.S. law):

1. The_Doc_Man stated that "The same principles apply to copyright law, patents, and trademarks." That statement is somewhat misleading - while copyrights, patents and trademarks are all elements of intellectual property, and many common principles apply to all three of them, they are in fact different types of protection, which are usually applicable to different sorts of property, and which are governed by different statutory and common law principles.

2. A basic principle of copyright law is that copyright does NOT protect an idea, but only a particular EXPRESSION of an idea. Thus, you could write an original short story about boy meets girl, boy loses girl, boy wins girl back and marries her, and, assuming the statutory requirements were met, you could copyright that story. But you could not copyright the idea of "boy meets girl, boy loses girl, boy wins girl back and marries her."

As applied to your friend's database design, it seems doubtful that the design itself (being essentially an idea) could be copyrighted, though your friend's particular expression of that design (text, drawings, etc.) might well be. Anyone can put a copyright notice on anything, but that alone doesn't mean that the copyright is valid or enforceable.

In general, designs are more likely to be eligible for patent protection, but your friend might also face signifcant hurdles in that pursuit:

1. In contrast to copyrights, which are easy and cheap to register, patent applications are complex and specialized, and undergo lengthy review by the Patent and Trademark Office. For most inventors, this means spending thousands of dollars and hiring a patent attorney or patent agent.
2. Even when a patent is granted, it has a good chance of being challenged in court by a corporation that can spend thousands (or millions) of dollars in hopes of invaliding the patent; such challenges are often successful, sometimes for no more reason than the inventor simply cannot afford the legal fees necessary to competently defend the patent.
3. In order to be patented, the design or invention must be both novel and non-obvious to persons knowledgeable with the applicable area of technology. At this late date, it's difficult to imagine that a generic normalized database would qualify.

 

[Susan Owen]

Hi Doc Man! Thanks very much for your informative answer. I will save it and "chew it" some more. My grandfather having been a patent lawyer, ah well, he is no longer available to ask questions to, but anyway, I think as long as I don't copy his field names, I could use the general idea. I really think he got it from soemwhere else anyway.

 

[Susan Owen]

Hi Alan, Thank you as well for your very informative answer. I think it's pretty safe to say that this is a very grey area, and not something that someone can really have the corner of the market on. Besides, I would make some changes to the design, as well as the implementation and it would be my own creation / application. It's a very good idea, and I'm sure it's somewhere out there on the Internet, but I just haven't been able to find it yet. Sharepoint is not anything like this at all, as far as I can determine.

 

[Susan Owen]

I'd be very interested in comparing notes with you. Basically, how it works is to categorize things on 3 levels. These levels could be anything. I'll take a travel agency, for example. The first level might be "Tours". The second level might be the starting date /destination, something that easily uniquely identifies it, (all this is also done with keys, but the text is for human identification in lists). Then the third level would anything you needed to track on it, like the person id (foreign key to a persons or contact table), registration date, payment received, passport obtined, shots necessary, just anything that applies to this particular tour, and all these fields are assigned separately for each tour.

Personal or Company information are separate tables. The generic part only handles things that, I guess aren't generic.

Another use would be, for example, in a service organization, the category of "Officers". In that case, the second level would be the type of officer, and the 3rd level would be the person's id, start date in office, geograpical region, or the geographical region might also be the 2nd level instead of the type of officer (that would make more sense).

Another top level might be membership, with the membership type being the 2nd level, and the third level group being, beginning of membership, end of membership, former membership category (in case of a total membership revamp).

As for membership fees, this would probably be handled entirely separately with yet another 1st level perhaps called invoicing. Then the second level to that would be types of invoices (membership invoices, sponsoring invoices, events invoices (which would include an event ID), then the third level being the group level of person's id, amount of invoice, date sent, date 1st reminder, date 2nd reminder, date 3rd reminder, date payment received,

As for events being a top level group, these would be handled similarly to the way tours would be handled, the 2nd level being the event itself, the third level group might include a seating assignment as well as registration date, special meal requirements, credit card payment, charge date, might also include a comments field.

Just some possible implementations I could come up with, entirely on my own.

 

[Susan Owen]

-No. The purpose of a relationship is to provide the information necessary to enforce referential integrity. If you are not enforcing RI, there is no need to go to the bother of defining relationships although Access does use the defined relationship to "help" you when you create queries. Access will automatically draw join lines if relationships are defined.

As to your 3D database design. It already exists. It is called Pick and is considered old technology.

Hi Pat, another queestion for you. Could you possibly point me to an example of this "Pick" as well as an example of "Sharepoint" table design, I did search a bit but wasn't able to come up with anything tangible. Or if you could just describe what you know of it?

 

[Susan Owen]

Hi again Alan, sorry for the multiple postings, but you got me inspired here. Some more implementations I thought of:

Also add company size to the membership information.

Add transfer history to membership info? Or handle this with statistics. So another category might be separate for statistics, transfer from one category to another, pending resignation notice,

Sponsorship history (but this could come from the invoices, unless it was a non-monetary type of sponsorship.

Add specialty to the officer’s information, for example, a special area of law, banking, insurance, etc.

Shipping example, inventory levels? The top level might be products. The next level would be each particular product name, or better yet, product sales. The third level would be product id, customer id, quantity ordered, date ordered, date shipped.

Inventory (of product, supplies, or order history) would be another 2nd level group,

Another group would be product descriptions, product id, product name, supplier id,

Another group would be 2nd level to products would be suppliers: supplier id, (all the rest of the generic supplier info could be stored in the companies or contacts or persons type table. Only necessary for the suppliers (2nd level of products) might be lead time in ordering, payment methods, or the lead time in ordering might also be included with the product description itself. Other supplier information might include a purchasing contact (person’s id in the contacts table), customer order number, taxes or currency if this is varied among suppliers, office hours,…

Cleaning hours worked, Top level might be Employees Work hours, second level might be job title, third level would be employee id, day, start time, end time, location (cleaning object number, building location as object)

 

[Pat Hartman]

SharePoint tables are something like:

UniqueRowID (a unique identifier for the row)
GroupID (what would in a normal table be the primary key)
FieldName (the name of the field)
FieldValue (the value of the field)

So, instead of a table having 20 columns per row, each column would be stored as a separate row in the SharePoint table.

I would have to search to find Pick info also. I never worked with it myself. I only know a little about it because I had people on my team who were programming with it. Pick allowed fields to contain "tables" and I believe that they could be nested. So they were essentially, embedded multi-dimentional arrays but I'm pretty sure they weren't stored that way or the DBMS would have been too slow to be useful.

 

[Ron_dK]

Maybe this might help :

http://www.answers.com/topic/pick-system

or :
http://en.wikipedia.org/wiki/Pick_operating_system