On Sun, Nov 14, 2010 at 9:07 PM, Gary Doades <[email protected]> wrote:
> On 13/11/2010 9:48 AM, flebber wrote: > >> Hi >> >> Looking for a little assistance. I am trying to create a basic >> database schema using Ruby, Sequel and Sqlite3. >> >> I am reading here >> http://sequel.rubyforge.org/rdoc/classes/Sequel/Database.html#M000510 >> and http://www.firstsql.com/tutor6.htm . >> >> The only thing that would be unusual about my db is that I want to use >> two primary keys time& date as no more than one event can occur at >> any time though multiple events could occur on a day. I am not >> understanding in sequel how to correctly create a db with multiple >> keys? Or should I just be generating a random number to create a key >> which references date and time? >> >> I would be very reluctant to use a random number as the primary key as > you can never guarantee that you aren't going to generate the key more than > once. Then you'll end up having to put in special code to handle those > conflicts. > > The more usual design here would be to have the id (primary key) column as > an integer autoincrement so that each time you insert a new record you get > the next higher id automatically assigned by the database engine. This also > plays well with how Sequel models work. > > I notice you also have an "EventNumber". Can this be the integer primary > key (with autoincrement). That way you'll have your primary key and > automatically generate your unique event numbers. > > As for the date & time columns, just put a unique index on them. That will > also satisfy your unique requirement for those columns. > > Cheers, > Gary. > > > -- > You received this message because you are subscribed to the Google Groups > "sequel-talk" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]<sequel-talk%[email protected]> > . > For more options, visit this group at > http://groups.google.com/group/sequel-talk?hl=en. > > >I would tend to use a single timestamp field instead of the 2 date and >time fields, though with SQLite's poor datetime handling you'd need to >jump through a couple of hoops to query for events on a particular >day. I guess in that case it makes sense to use distinct fields. This concerns me as date time searching is one of the main searches I will be performing. Any suggestions for an alternative to Sqlite, networking supports is not important and why I had initially opted for SQlite. >I notice you also have an "EventNumber". Can this be the integer primary key (with autoincrement). That way you'll have your primary key and >automatically generate your unique event numbers. Event number can't auto increment as it is not unique there could be 3 event ones on the same day but at differing locations -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sequel-talk?hl=en.
