What is the best method to restrict certain entries from taking on certain values
sqlite add tag
Anonymous 776 
First time reader.  First time poster to TopAnswers.

Thank you for reading this question, despite my poor title.  I could not think of a succinct and accurate title.

I am building a small sqlite database for a personal project.  I am using Python and SqlAlchemy.  I'm doing this mostly for the challenge and for the self education.

I have a table "components".  One of the attributes of this table is "type".  "type" is restricted to only three values, which I am going to enforce through SqlAlchemy's enum.

Each of the three "types" have "rates" associated with them.
Type "A" is measured in "parts / second"
Types "B" and "C" are both measured by the amount of time it takes to make one part (seconds / part).

I am not sure of the most appropriate way of handling the difference in rate measurements.

Option 1:
Should I simply invert A to be like B & C (or vice versa).  The reason I am a little leery about this option is rounding errors.

Option 2:
If I decide to not simply change the rates measure (like in option 1), do I store the raw numbers in a "rate" attribute and then use my Python code to determine how to apply the numbers? or do I set up two tables which resemble something like a lookup table, one to store the rate values of A and the other to store the rate values of B and C (since they are both measured in seconds / part)

Is there a better way than these two options?
Top Answer
James 
If I'm understanding your question, the keystone is this bit:


> I have a table "components". One of the attributes of this table is "type". "type" is restricted to only three values

You can use a foreign key constraint. In the design below, the components table will only accept a component type if it already exists in the component_types table.

		if object_id('component_types') is not null begin drop table components drop table component_types  end

		create table component_types (comp_type_id int identity (0,1) primary key, description varchar(255))

		insert into component_types (description)
		values('Doodad'),
		('widget'),
		('gyrothingy')

		select * from component_types

		create table components (comp_id int identity (0,1) primary key, comp_type_id int,
		foreign key(comp_type_id) references component_types(comp_type_id))

		insert into components (comp_type_id)
		values (0),
		(1),
		(2),
		(2),
		(1),
		(0)

		select * from components c
		inner join component_types t on c.comp_type_id = t.comp_type_id

		--this insert will fail
		insert into components (comp_type_id)
		values (3),
		(4),
		(5)

		select * from component_types
comments
transcript

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.