Any Database design experts?

[nonstandard]

Are there any database gurus out there could help me with designing a beekeeping database (for personal use only) I'm struggling with 1 to 1 relationships; data not personal

sent from my mobile.

 

[Monsieur Abeille]

Feel free to send me a PM with what you are after - 1st class honours in the thing though it was a few years ago

 

[Drew]

Are there any database gurus out there could help me with designing a beekeeping database (for personal use only) I'm struggling with 1 to 1 relationships; data not personal

sent from my mobile.

Post you question and you'll probably get some answers.......

1-1 means (pretty much) what it says on the tin.
One entity relates directly to another
eg NI Number => Person

 

[For want of a better word]

www.beetight.com

may help?

 

[Appinpiper]

This may help

If you register with the Bee base you can keep your records for free

https://secure.fera.defra.gov.uk/beebase/public/register.cfm

Hope it is some help

 

[Nijal]

There are relatively few occasions when you would construct a 1-1 relationship within a database. It is much more common to have a 1-many. For example, one beekeeper may have several apiaries or each apiary could have several hives, or each hive could have many time/date related records such as disease diagnostics, treatments, honey yields, visits and so on. Do you have a clear rationale for having the 1-1 relationship you are struggling with?

 

[Hombre]

@NonStandard,

I think that you need to have an idea of what it is you want from your database system and perhaps begin by defining a few of your entities (tables and their attributes (columns)).

Next you determine the relationships between the tables and end up with an entity relationship drawing. This process will expose missing connections and identify foreign keys that you will need to create/declare in order for your database lookups to be efficient. On the basis that you might become a huge beefarmer with thousands of colonies, then you don't want to spend your life table-scanning, so identifiying these keys early is fairly important.

A few questions to help your advisors and minimise the noise:
What OS do you intend to use?
Have you chosen a DBMS that you wish to use and if so which one is it?

What are the entities that you envisage using? Apiaries, queens, colonies, boxes/equipments, hive_configurations, yields.
= = =

You may like to have a play with XAMPP for Windows, Mac or Linux. The benefits are that it can be installed and then binned by the deletion of a directory/folder if you want to start again. All the difficulty of the installation is taken care of by the package.

http://www.apachefriends.org/en/xampp.html
Many people know from their own experience that it's not easy to install an Apache web server and it gets harder if you want to add MySQL, PHP and Perl.

XAMPP is an easy to install Apache distribution containing MySQL, PHP and Perl. XAMPP is really very easy to install and to use - just download, extract and start.

= = =

one to one relationship: A man has one natural father
one to many relationship: A man may have zero to several children
Notice that one to many relationships are asymetric.
Many to many relationships are inevitably a problem area and indicate a degree of complexity beyond your control or needing a rethink.
Don't construct data tables allowing nuls, it seems attractive but is a black hole that will swallow you. Nulls may be permitted in result_tables and can decide how to process the result, but storing null data is a copout that will bite your bottom.

Ingres RDBMS application programmer 1990 - 2003.

I have seen many graphic tools used to create, maintain and report database content, but invariably I have fallen back to reading the SQL code in order to gain an understanding of what is going on. It is normal to draw representations of your tables with lines joining the appropriate keys. It is these diagrams that keep you sane when joining a number of tables together. If you are joining more than seven tables, then maybe you need to review things. Avoid "products"(?), where each entry in a table lists every entry in another table. this is usually due to a lack of detail in the WHERE clause.

Sent from my Sempron 2200+ running . . . . . and running.

 

[Hombre]

This is a tutorial that might be of some value to you, even if you just skim through it.
http://www.atlasindia.com/sql.htm

 

[nonstandard]

Sorry for the absence guys, sometimes life seems to get in the way of these projects of mine.

Yes I use beetight, It seems to be a good app and Matt (AKA Sphex) is doing a sterling job. However, it just doesn't have all the items or style of recording that I want; I don't feel I can expect Matt to provide a totally custom solution for me.

Again the beebase solution is limited in the data that can be stored.

From my research it seems that 1:1 relationships are frowned upon as are m:m relationship,s m:m's can be overcome by adding an intermediate relationship. I can only perceive the queen:colony relationship as 1:1 in both directions without any way of changing that.

I have done some basic database construction before using access and for this project have defined my entities Apiary, Colony, Queen, Inspection and their various attributes. The db is normalised and primary and foreign keys defined it's just that this 1:1 relationship concerns me.

I guess if I simplify what I am asking it's basically 1:1 yes or no and if no then how do I work round it?

 

[Monsieur Abeille]

A 1:1 is normally superfluous, however there is no practical reason why you shouldn't use it if you prefer the design. In theory, with a huge database you are creating extra processing, but in practise for personal dbs it will not make a jot of difference.

Some things to consider re the queen/colony relationship:

• It is possible for a colony to exist for quite some time without a queen and not all colonies have a queen all of the time
• Some colonies may have more than one queen for a limited time
• It is quite possible to requeen a colony with a queen from another one

So this may not be a simple 1:1

I think the best design would indeed seperate the queen from the colony for these reasons and probably others I've not thought about.

 

[nonstandard]

Thanks MA,

These were my thoughts exactly, its a 1:1 but does have exceptions.

The queen as an entity also has attributes that only need to be recorded once rather than multiple times if the queen is moved around. These attributes would include race, year, mark colour, mother, origin etc.

So a separate table for queens it is, now to design some forms, queries and reports

 

[rae]

The 1:1 relationship between queen and colony is fine - it is an aspect of the function you want to design. In fact, there are a bunch of edge cases where your 1:1 is a 1 to many - supercedure for example. Also you may have a queenless colony with a 1:0.

Queen is not an attribute of colony, and colony is not an attribute of queen. Thus they are both entities. The attributes of the queen are certainly not attributes of the colony.

So, yes, two entities, probably with queen ID as an attribute of colony. You could do it the other way round, but most people think of the colony the main "function" rather than the queen.

 

[alanf]

The queen as an entity also has attributes that only need to be recorded once rather than multiple times if the queen is moved around. These attributes would include race, year, mark colour, mother, origin etc. So a separate table for queens it is

The queen has an existance outside any specific colony. She can be transferred, stored in a nuc or mating hive etc. I'm guessing that what needs to be recorded is the presence of a queen in a colony. The basic attributes are the start and end dates and the foreign keys are queen and colony. You might want to record laying here since it will be dependent on both the queen and colony.

Physically then, there are 3 tables: Colony, Queen and Queen-in-colony which might have a less dba inspired name like Laying_record if that was what you wanted to include. A Colony has one or more laying records from various queens. A queen has zero or more laying records in various colonies. A direct colony to queen relationship is possible at any given time instant but not a good idea in a database. You would want to record a history of when the queen was in another colony or when a colony had other queens and that needs at least three tables.

There is not much point going into the bodges because you're starting with a clean database. We are way below the scale where a logical entity model is helpful. You do need the warnings that come with any database work: there are no perfect solutions in the real world but there will be long and bitter arguments over the compromises.